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.

Calculating Variance Percentage in Excel

When calculating variance percentage in Excel, it's important to take into account the sign of the numbers. Here's how you can calculate variance percentage even when the figures are negative:

1. First, subtract the old figure (b) from the new figure (a) to get the difference. For example, if your old figure is -100 and your new figure is -50, the difference would be 50 (-50 - (-100) = 50).
2. Next, divide the difference by the absolute value of the old figure (|b|). In our example, the absolute value of -100 is 100, so we would divide 50 by 100 to get 0.5.
3. Finally, multiply the result by 100 to get the variance percentage. In our example, we would multiply 0.5 by 100 to get a variance percentage of -50%.

So the formula you would use in Excel would be: This formula will give you the correct variance percentage, taking into account the sign of the numbers.

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

Using absolute values should solve your problem.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MJ" wrote:

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.

Shouldnt that be

=(A1-B1)/A1

If this post helps click Yes
---------------
Jacob Skaria


"MJ" wrote:

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.

I think it is

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

--
Yanick


"Jacob Skaria" wrote:

Shouldnt that be

=(A1-B1)/A1

If this post helps click Yes
---------------
Jacob Skaria


"MJ" wrote:

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.

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

"Kim" wrote:
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 -----

"Kim" wrote in message
...
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