Apex Code Development You need to sign in to do that
Don't have an account?
AdminNewbie Need help with report formula
I have written a formula to calculate commission however, the formula needs to use the total amount of opportunites won to be accurate. How to i do this? I am having trouble putting it into a summary formula field and if i put it into opportunites it only runs using 1 opportunity amount.
This is the formula:
IF ( MRR__c <800, 0,
IF ( MRR__c <1000, (MRR__c - 800) *1.25,
IF ( MRR__c <1200, (MRR__c -1000) *1.5+ 250,
IF ( MRR__c <1400, (MRR__c - 1200) *1.75 + 550,
IF ( MRR__c <1600, (MRR__c - 1400) * 2 + 900,
IF ( MRR__c <1800, (MRR__c - 1600) *2.25 + 1300,
IF ( MRR__c <2000, (MRR__c - 1800) *2.5 + 1750,
IF ( MRR__c <2200, (MRR__c - 2000) *2.6 + 2250,
IF ( MRR__c <2400, (MRR__c - 2200) * 2.7 + 2770,
IF ( MRR__c <2600, (MRR__c - 2400) *2.8 + 3310,
IF ( MRR__c <2800, (MRR__c - 2600) *2.9 + 3870,
IF ( MRR__c <3000, (MRR__c - 2800) *3 + 4450,
IF ( MRR__c <3500, (MRR__c - 3000) *3 + 5050,
IF ( MRR__c <4000, (MRR__c - 3500) *3 + 6550,
IF ( MRR__c <= 5000, (MRR__c - 4000) *3 + 8050, 0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
ANy help would be much appreciated!! This has really become a brain teaser for me :)
This is the formula:
IF ( MRR__c <800, 0,
IF ( MRR__c <1000, (MRR__c - 800) *1.25,
IF ( MRR__c <1200, (MRR__c -1000) *1.5+ 250,
IF ( MRR__c <1400, (MRR__c - 1200) *1.75 + 550,
IF ( MRR__c <1600, (MRR__c - 1400) * 2 + 900,
IF ( MRR__c <1800, (MRR__c - 1600) *2.25 + 1300,
IF ( MRR__c <2000, (MRR__c - 1800) *2.5 + 1750,
IF ( MRR__c <2200, (MRR__c - 2000) *2.6 + 2250,
IF ( MRR__c <2400, (MRR__c - 2200) * 2.7 + 2770,
IF ( MRR__c <2600, (MRR__c - 2400) *2.8 + 3310,
IF ( MRR__c <2800, (MRR__c - 2600) *2.9 + 3870,
IF ( MRR__c <3000, (MRR__c - 2800) *3 + 4450,
IF ( MRR__c <3500, (MRR__c - 3000) *3 + 5050,
IF ( MRR__c <4000, (MRR__c - 3500) *3 + 6550,
IF ( MRR__c <= 5000, (MRR__c - 4000) *3 + 8050, 0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
ANy help would be much appreciated!! This has really become a brain teaser for me :)
I hope I understand your question correctly...
I know you indicated that putting that formula into a custom Opportunity field would only produce a result for one Opportunity, and that is true. But couldn't you build that field on the Opportunity and then check the SUM box on that field in the report in order to get the grand total you're looking for?
Got it - your formula has to calc on the total of Opportunities rather than each individual Opportunity.
What kind of field is MRR__c right now? Is it an Opportunity field? an Account field? Is it a formula field? what type?
What field are you summarizing in your report? (I'm assuming it's the recipient of the commission - the Opportunity owner?)
Unfortunately, I just learned that custom summary formulas are limited to 500 characters. I had to shorten your formula significantly in order to run a quick test. That said, here is the format you'll need to use in your custom summary formula:
IF(Opportunity.MRR__c:SUM <800, 0,
IF(Opportunity.MRR__c:SUM <1000, (Opportunity.MRR__c:SUM - 800) *1.25,
IF(Opportunity.MRR__c:SUM <1200, (Opportunity.MRR__c:SUM -1000) *1.5+ 250,
IF(Opportunity.MRR__c:SUM <1400, (Opportunity.MRR__c:SUM - 1200) *1.75 + 550,
IF(Opportunity.MRR__c:SUM <1600, (Opportunity.MRR__c:SUM - 1400) * 2 + 900,
IF(Opportunity.MRR__c:SUM <1800, (Opportunity.MRR__c:SUM - 1600) *2.25 + 1300,99))))))
One other note... I used 99 as the final else value. Your formula as you originally wrote it would calc 0 for anything over 5000, and a sales guy with a great month isn't gonna like a 0 paycheck :-). I'm not sure what you want to use as your else value, but I assume your commission bracket tops out somewhere, so that should be the final else value.
Okay, one more note... you may want to check next Monday to see if custom summary formula character limits are raised with the Spring '08 release. I know that validation formula character limitations are increasing from 1300 to 3900. Hopefully custom summary formula character limits are raised as well! Good luck.