How do I calculate variance percetage if figures are negative

[MJ]

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.

 

[Luke M]

Try
=(ABS(A-B)/ABS(A))*100

Using absolute values should solve your problem.

 

[Jacob Skaria]

Shouldnt that be

=(A1-B1)/A1

If this post helps click Yes

 

[Yanick]

I think it is

=abs(A1-B1)/abs(A1)

--
Yanick

Shouldnt that be

=(A1-B1)/A1

If this post helps click Yes

 

[Kim]

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 persume is a simple deduction =
270,720 - 192,000 = $78,720. But now I need to work out the percentage
variance?

Any suggestions?
Thanks in advance,

Kim

 

[JoeU2004]

Budget amount $270,720
Actual amount $192,000

I need to work out the variance which I persume is a simple deduction =
270,720 - 192,000 = $78,720. But now I need to work out the percentage
variance?

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.


----- original message -----