ABS or IF statement

[darrenciccone]

or maybe both?

I have hyperion linked data and i am doing % increase/decrease and when I have 0 sales units for 08 and 0 sales units for 09 im getting a -100% what I would like is to limit my downside variance to -100% and have the +variance unlimited; I have tried various min/max formulas to no avail; any help is greatly appreciated the last formula I tried to use from a suggestion on this board was:

=IF(AND(AT12=0,AS12=0),0,MIN((AS12/MAX(AT12,1))-1,1))

but it still gave -100% when 0 and 0 was picked up; could it be something to do with hyperion linked cells??? thanks for any help!

 

[Gerald Higgins]

I can't replicate this.

Are you SURE that AT12 and AS12 BOTH contain zero ?

Or is it possible that one of them perhaps contains something like 0.000001 ?