Formula to work out Variance % (Forecast to Actuals)

[James8761]

Hi,

I am trying to work on a Forecast spreadsheet. I have in column E my new forecast for Q3.

In Column I, I have my Q1 Actuals. In column M I have the difference between the Forecast for Q3 and the Actuals for Q1. In column N I have my Q2 Actuals. In column O I have the difference between the forecast for Q3 and the Actuals for Q2.

In Column P I am trying to put a % variance based on if a figure is up or down comparing Columns M and E.

In Column Q I am trying to put a % variance based on if a figure is up or down comparing Columns O and E.


I have a formula that states this =M14/E14

And then I treid =IFERROR(IF(AND(I27=0,E27>0),-1,M27/I27),0)

This works fine in general. The problem is when one number is a negative and one a positive.

Is there a way around. I have read about ABS, but can’t get it to work.

If I can explain better please let me know, just want to get something in before we go on lockdown!

Kind regards,
James

 

[tyija1995]

Which numbers are concerning you about their sign?
You can nest an ABS function as you say around it, for example if it is the M27/I27 part, write it as ABS(M27/I27)

In the case of M27 = 5 and I27 = -2.5 then ABS(5/-2.5) = 2

 

[James8761]

Hi,

So for my Q3 Forecast in Cell E66 I have 309. The Q1 Actual in Cell I66 is 15. The Variance in Column M66 is 293 and the result of the formula is 1896%. Seems fine.
However,
For my Q3 Forecast in Cell E66 I have 309. The Q2 Actual in Cell N66 is -41. The Variance in Column M66 is 350 and the result of the formula is 848%. Seems wrong.

 

[tyija1995]

Sorry I'm not too good with financial stuff, what result would you expect from the formula instead of 848% ?