Forum Discussion

Copper Contributor

Oct 25, 2021

Solved

Ignoring zero in IF formula

Hi all, I'm a novice in Excel so go easy on me, but this little thing has me pulling out my hair! I'm creating a scatter chart using error bars to draw between the scatter markers, so I need to tak...

excel

Formulas and Functions

HansVogelaar
Oct 25, 2021
joeexception

For example

=IF(A3=0,0,IF(A3-A2<0,ABS(A3-A2),0))

or

=IF(OR(A3=0,A3-A2>=0),0,ABS(A3-A2))

Riny_van_Eekelen

Platinum Contributor

Oct 25, 2021

joeexception You're a novice. I'm not a clairvoyant but perhaps theses will do what want:

Positive: =IF(A3=0,0,IF(A3-A2>0,(A3-A2),0))

Negative: =IF(A3=0,0,IF(A3-A2<0,ABS(A3-A2),0))

Or perhaps you can combine both into one:

=IF(A3=0,0,IF(A3-A2>0,(A3-A2),ABS(A3-A2)))

Ooops: Didn't see the first response until I posted mine.

joeexception

Copper Contributor

Oct 25, 2021

Thanks Riny - Hans got in just before you 😉
Too many clever boffins on this site