How to get negative variance percentage figures in Microsfot Excel

[Zebadiah]

I am trying to calculate the variance percentage in a profit & loss account in excel. I am using the formula (a-b/a)*100, which works ok except when one or both of the figures is negative. The result is showing positive instead of negative and vise-versa.

[Gania]

Try =(ABS(A-B)/ABS(A))*100 .Using absolute values should solve your problem.

[Shurod]

Can you please help me someone PLEASE!! I have been trying to work this out for 8 hours and coffee is now very low. I am trying to work out the following : Budget amount $270,720 / actual amount $192,000. I need to work out the variance which I presume is a simple deduction = 270,720 - 192,000 = $78,720. But now I need to work out the percentage variance.

[Erakna]

Well, for starters, I think the difference is -78,720, computed as 192,000 - 270,720. Negative corresponds to "under budget", and positive corresponds to "over budget". It is just a matter of point of view. But reversing the sign makes my head spin. So, if the budget amount is A1 and actual amount is A2, the percentage difference is: =(A2 - A1) / A1. It can also be written as A2/A1 - 1.
Excel statistical functions: VAR

[DanielaA]

Excels help can help as exceptional times further. The variance can be found in fact this term. Standard deviation is STDEV in Excel. How to enter a formula, you probably found out already, right. Otherwise, Excel also has a statistical analysis function. Which is not installed by default. You can find them under Tools in Excel 2003 - re-install> click analytic and possibly -> Add ins. I hope to bring you further, otherwise just mail again.