Help with IF formula

[willow1985]

I have the below formula:

=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2))

This formula works well however I want to add one more calculation. I want to subtract W2 from the result the above formula gives, there is just a couple of problems:
1. W2 could be blank
2. W2 could be 0

So I get #Value error when W2 is 0 or blank. Is there anyway to avoid this error? I only want to subtract W2 if there is a value above 0 and is not blank.

Thank you to anyone who can help me with this

Carla

 

[GraH]

Try,
=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2) - (W2*and(W2<>"", W2<>0)))

or even IF(N2=0,"",IF(S2="0",N2-G2,N2-S2) - (W2*(W2<>"")))

 

[Eric W]

Try:

=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2))-N(W2)

 

[GraH]

I knew that, so why didn't I think of that??

 

[willow1985]

Try:

=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2))-N(W2)

The above formula is what I originally tried but would not work as it gives the #Value error because
1. W2 could be blank and
2. W2 could be 0

GraH formula works perfectly as it accounts for W2 being blank or zero.

Thanks guys!

 

[willow1985]

Try,
=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2) - (W2*and(W2<>"", W2<>0)))

or even IF(N2=0,"",IF(S2="0",N2-G2,N2-S2) - (W2*(W2<>"")))

Your formula works perfect as it accounts for W2 being blank or 0. I chose to use the 2nd version and it seems to work great.

Thank you very much!

 

[Eric W]

The above formula is what I originally tried but would not work as it gives the #Value error because
1. W2 could be blank and
2. W2 could be 0

GraH formula works perfectly as it accounts for W2 being blank or zero.

Thanks guys!

You tried it with the -N(W) ?

=IF(N2=0,"",IF(S2="0",N2-G2,N2-S2))-N(W2)

The N() function is an old function originally used for compatibility with Lotus 1-2-3. It converts anything to a number. If it's already a number, no change. If it's text, such as a blank, it converts it to a 0. That will get rid of the #VALUE error.