Variance Report Percentage Error

[kzappe]

Hello Board,

I am adding a percentage variance column in excel to our monthly P&L.

COL A = Actual
COL B = Budget
COL C = $Variance
COL D = %Variance

My issue comes when I have $0.00 in both COL A and B (no budget or variance in that month), I want a 0% not the 100%. Here is the formula I have:

=ABS(IF(B,C/B,"100%"))

What am I missing to get 100% variance if COL A or B have a $0 but 0% if COL A AND B are $0.

Thanks!

 

[Scott Huish]

=ABS(IF(B2,C2/B2,1-(COUNTIF(A2:B2,0)=2)))

Format as %

 

[SpillerBD]

You are not really evaluating the value of B
Also, you data types are different. If TRUE, calculate a number, if FALSE use text...

To restate your logic.
"if (COL A = 0 OR Col B =0) then 0 ELSE if (Col A = 0 AND Col B = 0) then 1 ELSE ABS(Col C/Col B)"

 

[Scott Huish]

The op is evaluating the value of B. Any non-zero number equates as TRUE, and zero equates as false.

=IF(B2,c2/b2,1) is a valid statement for example. It means if B2 is a non-zero value evaluate C2/B2 else return 1