Formula nesting with IF statements

  • Thread starter Thread starter sherlyn.moore
  • Start date Start date
S

sherlyn.moore

I have an actual vs budget spreadsheet. At the bottom I have the P/L and Ineed to express below the actual P/L the percent difference with the budget.
That is easy to do when both numbers are positive. But when one or the other, or both is negative, a simple division formula doesn't work. I created if statements that work for each of the three scenerios (neg/neg, pos/neg and neg/pos) but when I try to nest them into one very large if statement, instead of getting a percentage answer I get the answer of "false." Below are the four individual formulas and the nested formula. I would appreciateany help in figuring out where I am messing up.
Act/Bud= 14000/23000 ===> =A/B
Act/Bud= -14000/23000 ==> =IF(A<0,IF(B<0,ABS(A-B)/ABS(B),""))
Act/Bud= -14000/23000 ==> =IF(A<0,IF(B>0,-(B-A)/B),"")
Act/Bud= 14000/-23000 ==> =IF(A>0,IF(B<0,-(A-B)/B),"")
All of the above formulas give me the correct percentage difference; nesting them below I arrived at this:
=IF(A<0,IF(B<0,ABS(A-B)/ABS(B),IF(A<0,IF(B>0,-(B-A)/B),IF(A>0,IF(B<0,-(A-B)/B),A/B))))
Excel accepts this formula but gives me the answer of "False"
 
hi Sherlyn,

=IF(AND(A1>0,B1>0),A1/B1,IF(AND(A1<0,B1<0),ABS(A1-B1)/ABS(B1),IF(AND(A1<0,B1>0),-(B1-A1)/B1,IF(AND(A1>0,B1<0),-(A1-B1)/B1,""))))

isabelle
 
I have an actual vs budget spreadsheet. At the bottom I have
the P/L and I need to express below the actual P/L the percent
difference with the budget. [....]
I would appreciate any help in figuring out where I am messing up.
Act/Bud= 14000/23000 ===> =A/B
Act/Bud= -14000/23000 ==> =IF(A<0,IF(B<0,ABS(A-B)/ABS(B),""))
Act/Bud= -14000/23000 ==> =IF(A<0,IF(B>0,-(B-A)/B),"")
Act/Bud= 14000/-23000 ==> =IF(A>0,IF(B<0,-(A-B)/B),"")
Click to expand...

You probably do not need to use nested functions. I think what you want is:

=(A1-B1)/ABS(B1)

formatted as Percentage.

I think you made a mistake with your first example, Act/Bud= 14000/23000.
Your formula expresses Actual __as_a_percentage__ of Budget. If you want
the percentage __difference__, the formula should be (A-B)/B, similar to the
other formulas which do indeed calculated percentage difference.

I think you also made a mistake in your second example,
Act/Bud= -14000/23000. I think you meant Act/Bud= -14000/-23000. That is
consistent with your formula; and it is different from the third example,
which is also Act/Bud= -14000/23000.

However, if you allow for negative Actual and Budget, you must also allow
for zero. So a more a more-reliable formula is:

=IF(B1=0, SIGN(A1), (A1-B1)/ABS(B1))

formatted as Percentage.

That returns +100% if Actual>0 and Budget=0. It returns -100% if Actual<0
and Budget=0.

For "full disclosure", I should point out that your formula and my
alternative -- which is consistent with your intent, I believe -- is not
mathematically consistent in all cases. That is, if the formula in C1,
=B1*(1+C1) is not always A1, as it "should be" mathematically.

But I agree with your intentions, as I interpreted them. In effect,
movement to the right from Budget to Actual is positive percentage change;
and movement to the left from Budget to Actual is a negative percentage
change.
 
Back
Top