ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   =IF((I4="R");H4;IF((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);J4*E4) (https://www.excelbanter.com/excel-discussion-misc-queries/123719-%3Dif-i4%3D-r-%3Bh4%3Bif-h4-0-%3Bh4%2Ae4%3Bif-i4%3D0-%3Bj4%2Ae4%3Bif-j4-h4-%3Bj4%2Ae4.html)

IF(ISNA ERROR

=IF((I4="R");H4;IF((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);J4*E4)
 
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((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);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(J4H4);J4*E4;" ")
3. #VALUE!
=IF(ISNA(I17="R");H17;"");IF(ISNA(H4=0);G4+I4*E4; "");IF(ISNA(I4=0);J4*E4;"");IF(ISNA(J4H4);J4*E4;" ")


David Biddulph

=IF((I4="R");H4;IF((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);J4*E4)
 
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((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);J4*E4;""))))
or if you want a zero in that case, you can replace the "" with 0.
--
David Biddulph

"IF(ISNA ERROR" <IF(ISNA wrote in message
...
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((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);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(J4H4);J4*E4;" ")
3. #VALUE!
=IF(ISNA(I17="R");H17;"");IF(ISNA(H4=0);G4+I4*E4; "");IF(ISNA(I4=0);J4*E4;"");IF(ISNA(J4H4);J4*E4;" ")




kassie

=IF((I4="R");H4;IF((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);J4*E4)
 
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!
--
Hth

Kassie Kasselman


"IF(ISNA ERROR" wrote:

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((H40);H4*E4;IF((I4=0);J4*E4;IF ((J4H4);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(J4H4);J4*E4;" ")
3. #VALUE!
=IF(ISNA(I17="R");H17;"");IF(ISNA(H4=0);G4+I4*E4; "");IF(ISNA(I4=0);J4*E4;"");IF(ISNA(J4H4);J4*E4;" ")



All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com