+ / - Percentage Variation

[Vacuum Sealed]

Hi All

I use this formula in Column E ( %Gain + / - )

=IF(B4=0,D4,IF(D4<0,D4,IF(D4<>100,(D4-100),IF(B4=0,D4*C4))))

Column A = Day
Column B = Planned
Column C = Actual
Column D = %Actual
Column E = %Gain + / -
Column F = %RunningTotal

It does almost everything I desire, with one minor omition, It does not
return a ( 0 ) when the following condition happens:

If the Planned & Actual = the same, the there is ( 0 ) zero diff + / -

the problem is in the %Gain + / - is returning/displaying a "FALSE"
statement instead of displaying a zero, all the other equations work out
fine, just need to convert the False to a zero and it's finished.

TIA
Mick.

 

[Vacuum Sealed]

Just goes to show.

You show not try and do equations when you are tired....

=IF(B4=0,D4,IF(D4<0,D4,IF(D4<>100,(D4-100),0)))

Sorry for wasting time.

Cheers again
Mick.

 

[Clif McIrvin]

Just goes to show.

You show not try and do equations when you are tired....

=IF(B4=0,D4,IF(D4<0,D4,IF(D4<>100,(D4-100),0)))

Sorry for wasting time.

Cheers again
Mick.

Often when I'm building a nested (or otherwise "somewhat complex")
formula I will use multiple helper cells and build my formula "one piece
at a time" until I get the results I'm after ... then I put it all
together and test it again. I find it much easier to debug my logic
that way.

 

[Vacuum Sealed]

Clif

Make perfectly logical sense

Thank you.
Mick.