Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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;" ") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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;" ") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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;" ") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|