=IF((I4="R");H4;IF((H4>0);H4*E4;IF((I4=0);J4*E4;IF((J4>H4);J4*E4)

I

IF(ISNA ERROR

Is there anyway to write formula to get 0, or leave it empty when the #N/A
is a result in the other formula?Here is my Example:
1.Formula is working fine
=IF((I4="R");H4;IF((H4>0);H4*E4;IF((I4=0);J4*E4;IF((J4>H4);J4*E4)

2.This formula is not working
=IF(ISNA(I17="R");H17;"")&IF(ISNA(H4>=0);G4+I4*E4;"")&IF(ISNA(I4=0);J4*E4;"")&IF(ISNA(J4>H4);J4*E4;"")
3. #VALUE
=IF(ISNA(I17="R");H17;"");IF(ISNA(H4>=0);G4+I4*E4;"");IF(ISNA(I4=0);J4*E4;"");IF(ISNA(J4>H4);J4*E4;"")
 
D

David Biddulph

Your second formula will give a blank, as those ISNA() functions will always
return FALSE.
Your third formula is invalid syntax.
Your first formula should not work, as the parentheses don't match.
What are you trying to achieve? If you want a blank if none of your 4
conditions are met, you could use
=IF((I4="R");H4;IF((H4>0);H4*E4;IF((I4=0);J4*E4;IF((J4>H4);J4*E4;""))))
or if you want a zero in that case, you can replace the "" with 0.
 
K

kassie

Your syntax is totally wroing, that's why it does not work? =IF(ISNA(I17="R"
can never occur. I17 either ="R" or <>"R", no errors. To trap an I17 error
in this situation, you need to say =IF(ISNA(I17);scenario if it is an
error;scenario if it is not an #N/A error). If you are in fact rtying to
trap an error in H17, provided that I17 = "R", you should say
=IF(I17="R";IF(ISNA(H17);"";scenario if not an #N/A error. This will again
change if you are trying to test whether H4,I4,G4 or whatever has an error.
From hyour question it is not clear what you are trying to achive?

I suggest you repost, and tell us what it is that you are trying to achieve,
and I'm sure someone will be able to provide the correct formula!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top