# Variance Percentage Formulas - Can't Fix!

1. ## Variance Percentage Formulas - Can't Fix!

Hi All,

Hoping someone could help me, would really appreciate it.

I am an accountant and often have two variance columns when comparing against Actual results to Budget - One for absolute variances between the actual and budget and one for the percentage variance against the actual and budget.

The issue I have is, if I start the variance percentage formula with '=+' if the budget number is a negative and the actual number is a positive, the percentage will be a minus percentage instead of a plus and if both numbers are minus, e.g. actual is -1 and budget is -5 it will show a negative percentage instead of a positive and vice versa. To fix this, I've always had to keep changing the start of the formula to say either '=-' or '=+' depending on what the numbers are. Is there a way to fix this?

Furthermore, if I'm comparing an actual number of say 50 and a budget number of 0 the percentage will show the '#DIV/0!' sign instead of +100% and if I'm comparing an actual number of say -50 and budget number of 0 it will show the '#DIV/0!' instead of -100% so I always have to manually type in 100% or -100%.

If anyone could fix these 2 issues it would be an absolute life saver!!

Hope I explained this well enough and many thanks in advance!

Chris  Register To Reply

2. ## Re: Variance Percentage Formulas - Can't Fix!

Second problem:
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Variance Percentage Formulas - Can't Fix!

Thank you so much! That's perfect for 2nd problem, works great!  Register To Reply

4. ## Re: Variance Percentage Formulas - Can't Fix!

You're welcome. Thanks for the rep. I kinda think you could use the SIGN function to address the first problem, but I have to be honest and say I don't really understand your logic.  Register To Reply

5. ## Re: Variance Percentage Formulas - Can't Fix!

No problem.

I've attached an excel file with 4 different examples. Two of the examples work with the formula starting with '=+' and two don't. I would have the same issue if I started it with '=-' where two of the examples would be correct and two of them wouldn't be.

Hope this explains better?

Thanks,

Chris  Register To Reply

6. ## Re: Variance Percentage Formulas - Can't Fix!

I think I found a way to fix it! By using the formula '=F8/ABS(E8)'. Is there a way I can combine this formula and your formula together?  Register To Reply

7. ## Re: Variance Percentage Formulas - Can't Fix!

OK, it's taken me a while to get round to looking at your file, but I think this addresses the problem:
Formula:  `Please Login or Register  to view this content.`
This reverses the sign in the examples marked as incorrect, and leaves them alone in the examples marked as correct.  Register To Reply

8. ## Re: Variance Percentage Formulas - Can't Fix!

Is this resolved now?  Register To Reply