CALCULATING VARIANCE

  • Thread starter Thread starter Julian Campbell
  • Start date Start date
J

Julian Campbell

hello all,

I need to know how to calculate a - or + variance in a sheet

eg using cells A1, A2, A3, A4

A1 = EXPECTED FIGURE 10,000
A2 = ACTUAL FIGURE , 8,000
A3 = VARIANCE = - 2,000
A4 = VARIANCE 2,000 SHOWN AS A % LOSS

AND THE SANE AS ABOVE BUT WITH A2 ABOVE EXPECTED FIGURE SHOWN AS A +
VARIANCE AND SO SHOWN AS A % GAIN

Thanks a lot

Julian
 
Is this what you're looking for:

In A3,
=A2-A1

In A4,
=A2/A1-1
Custom format, +0.00%;-0.00%
 
Seems to work except for the % equation. This is how I input it

in A4 I typed =A2/A1-1 (WHAT DOES THE - 1 MEAN?)

Then I right cliked and chose format cells, chose custom from the drop down
list and entered the following +0.00%;-0.00%

Whatever the results in A3, A4 stays at +0.00%

Any ideas

Thanks again

Julian
 
Properly formatted, A4 will display +0.00% *only* when A1 and A2 contain the
*same* value.
If A1 and A2 are empty, you'll get a #Div/0 error message in A4.

There is no connection between A3 and calculations in A4.

What values do you have in A1 and A2?
 
I have used 3 variations to see the differance

I have in A1 = 10000 A2 = 8000 VARIANCE = -2000 A4 READS +0.00%

Then A1 = 10000 A2 = 11000 VARIANCE = 1000 A4 READS +0.00%

Then A1 = 0 A2 = 0 VARIANCE = 0 A4 = #DIV/0!

What am I doing wrong?

Thanks again

Julian
 
All I can suggest is open a brand new, empty sheet, and start all over
again, and see what you get.
 
Back
Top