SOLVED

Ignoring zero in IF formula

Copper Contributor

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 take my (simple) data and create a positive or negative error value to determine the length of the line:

Positive

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

Negative

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

The problem I have is that I don't want any error bars when 'A3' equals exactly 0. The positive formula is fine, but the negative one, of course, will still return a value (0 - 'A2').

I've tried a bunch of different "AND" and "NOT" things, and other nested IF statements, but I'm clearly missing something pretty simple here!

So, basically, is there a way of saying:

If (A3-A2)<0, return the absolute value of (A3-A2), otherwise return 0, UNLESS A3 is exactly 0, in which case always return 0? 

Thanks in advance!

 

 

  

 

   

5 Replies
best response confirmed by joeexception (Copper Contributor)
Solution

@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))

@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.

Outstanding! Thanks for your quick help Hans!
I just couldn't work out the order things needed to go in - knew there was something really simple I was missing.
Thanks Riny - Hans got in just before you ;)
Too many clever boffins on this site

@joeexception 

As variant

=MAX(A2-A3,0)*(A3<>0)

for negative

1 best response

Accepted Solutions
best response confirmed by joeexception (Copper Contributor)
Solution

@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))

View solution in original post