Apex Code Development You need to sign in to do that
Don't have an account?
Aiyaz Variance formula (a - b) / b
Was wondering if anyone has created a formula field to calculate variance?
Currently I created three fields:
Current Sales year to Date
Lasts year's Sales year to date
Variance
Lets say this years sales was $5000, and last years was 0 dollars, how would you account for this in the formula field since you cannot use 0 in the denominator?
Any help would be great.
Thanks in Advance
1800-5000/1800
%Budget Variance% = (Actual – Budget) / Budget
- Add an If() clause at the beginning - if it's going to have a 0 in denominator, return 0 and stop calculating
- Similar to #1, but instead, put a .01 in the denominator - this will return an absurdly high percentage, but some people prefer that to seeing a 0
- Make the formula field a text type of fomula, and then return an "N/A" or such if there's a 0 in the denominator. There are many drawbacks to working with it as text, but there is the advantage of being able to show an N/A
4th idea that occurred to me while writing, that I think I like the best. Do #1, then create a second formula field (this one is text, the first is a number) that looks at the results of #1 and returns "N/A" if there would a 0 in denom, but otherwise returns the result from #1. This way you can still work with the value as a number by referencing the first formula.Jakester,
I actually got it running yesterday using your Idea#1, where I just returned a "0" value if denominator's variable equalled 0. I was thinking of using the N/A, however when using the IF statement, if your dealing with Currency or Number, you have to return a currency or number it seems.
Thanks for the ideas!