|
IF ISERROR
Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function
within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. |
IF ISERROR
Nel post
*John Moore* ha scritto: Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. Hi John, I think you don't need ISERROR, you could use the second condition of IF: =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0),0) I make the assumption the cell in which the formula is written has been formatted as percentage, so you don't need to put inside the formula. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
IF ISERROR
If any of the lookups could fail, you could try
=IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,2,0 )/VLOOKUP($C11,fourth,4,0) ),"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") If it is just that the division might result in 0, then use =IF($A$3="4Q05",IF(VLOOKUP($C11,fourth,4,0)=0,"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. |
IF ISERROR
Hi Bob, your formula works fine ,,, thanks for that ,,,,one question though
,,,, what if I wnat to extend it to cover multpile IF's .... such as =IF($A$3="4Q05",VLOOKUP($C11,fourth,3,0)/VLOOKUP($C11,fourth,14,0),IF($A$3="1Q06",VLOOKUP($ C11,first,3,0)/VLOOKUP($C11,first,14,0),IF($A$3="2Q06",VLOOKUP($C 11,second,3,0)/VLOOKUP($C11,second,14,0)))) Can the IF ISERROR still be used ?? "Bob Phillips" wrote: If any of the lookups could fail, you could try =IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,2,0 )/VLOOKUP($C11,fourth,4,0) ),"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") If it is just that the division might result in 0, then use =IF($A$3="4Q05",IF(VLOOKUP($C11,fourth,4,0)=0,"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. |
IF ISERROR
Like so?
=IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,3,0 )/VLOOKUP($C11,fourth,14,0 )),"",VLOOKUP($C11,fourth,3,0)/VLOOKUP($C11,fourth,14,0)), IF($A$3="1Q06",IF(ISERROR(VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0)), "",VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0)), IF($A$3="2Q06",IF(ISERROR(VLOOKUP($C11,second,3,0)/VLOOKUP($C11,second,14,0) ),"",VLOOKUP($C11,second,3,0)/VLOOKUP($C11,second,14,0)),""))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi Bob, your formula works fine ,,, thanks for that ,,,,one question though ,,,, what if I wnat to extend it to cover multpile IF's .... such as =IF($A$3="4Q05",VLOOKUP($C11,fourth,3,0)/VLOOKUP($C11,fourth,14,0),IF($A$3=" 1Q06",VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0),IF($A$3="2Q06",VLOOKU P($C11,second,3,0)/VLOOKUP($C11,second,14,0)))) Can the IF ISERROR still be used ?? "Bob Phillips" wrote: If any of the lookups could fail, you could try =IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,2,0 )/VLOOKUP($C11,fourth,4,0) ),"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") If it is just that the division might result in 0, then use =IF($A$3="4Q05",IF(VLOOKUP($C11,fourth,4,0)=0,"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. |
IF ISERROR
Hi Bob ,,, works a treat, thanks again.
"Bob Phillips" wrote: Like so? =IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,3,0 )/VLOOKUP($C11,fourth,14,0 )),"",VLOOKUP($C11,fourth,3,0)/VLOOKUP($C11,fourth,14,0)), IF($A$3="1Q06",IF(ISERROR(VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0)), "",VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0)), IF($A$3="2Q06",IF(ISERROR(VLOOKUP($C11,second,3,0)/VLOOKUP($C11,second,14,0) ),"",VLOOKUP($C11,second,3,0)/VLOOKUP($C11,second,14,0)),""))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi Bob, your formula works fine ,,, thanks for that ,,,,one question though ,,,, what if I wnat to extend it to cover multpile IF's .... such as =IF($A$3="4Q05",VLOOKUP($C11,fourth,3,0)/VLOOKUP($C11,fourth,14,0),IF($A$3=" 1Q06",VLOOKUP($C11,first,3,0)/VLOOKUP($C11,first,14,0),IF($A$3="2Q06",VLOOKU P($C11,second,3,0)/VLOOKUP($C11,second,14,0)))) Can the IF ISERROR still be used ?? "Bob Phillips" wrote: If any of the lookups could fail, you could try =IF($A$3="4Q05",IF(ISERROR(VLOOKUP($C11,fourth,2,0 )/VLOOKUP($C11,fourth,4,0) ),"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") If it is just that the division might result in 0, then use =IF($A$3="4Q05",IF(VLOOKUP($C11,fourth,4,0)=0,"", VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)),"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Moore" wrote in message ... Hi guys, looking for some help ,,,,, I am trying to use the ISERROR function within a formula to return 0% if the data returned is an error ,,,,,, formula is =IF($A$3="4Q05",VLOOKUP($C11,fourth,2,0)/VLOOKUP($C11,fourth,14,0)), which in itself works fine, but can't get the iF ISERROR to work with it. |
| All times are GMT +1. The time now is 05:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com