% Difference Calculation Issue 2

[NumberCrunchingMonky]

What would a formula look like to solve the following issue?

Plan Actual Difference
5,000 1,000 4,000

What I want to do is this:

If the Actual is 20% above of below the plan, then return the difference of the two numbers (if the actual was above plan, then the number would be positive; if the actual is below plan then it would be negative).

Thanks.

NCM

 

[bluedragon2]

A1 = Plan
B1 = Actual

=IF(A1>B1,IF(B1/A1>=0.2,B1-A1,""),IF(A1/B1>=0.2,A1-B1,""))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[NumberCrunchingMonky]

Hi Blue:

Thanks for the formula. It works well, but I neglected to include a caveat: if the plan value is "0" then I get a Div# error; further, I'd like to add "if Actual is 20% above or BELOW plan (I think I left that out of the original post).

I know the "IF(ISERROR(" trick, but integrating it in this formula is beyond me.

Sorry. Thanks.

NCM

 

[TonyJollans]

Hi NumberCrunchingMonky,

What about ..

[blue][tt]=IF(OR(B1>A1*1.2,B1<A1*0.8),B1-A1,"")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[TonyJollans]

.. or ..

[blue][tt]=IF(ABS(A1-B1)>A1*0.2,B1-A1,"")[/tt][/blue]

.. both of which avoid the division problem.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NumberCrunchingMonky]

Fantastic. Thanks, Tony for your help. It works perfectly. The ABS function is (for lack of a better word...) "neat".

And thanks again, Blue. I did a search on old threads for this and saw a post of yours that suggests a test of: IF(ISERROR(0/A1," "... which was brilliant. That's now on my hack sheet for future use.

Thanks.

NCM