iserror
 

I am using LOOKUP to obtain values from another sheet, this formula is
replicate 3 times. In most cases at least one of the three formulas will
return #N/A, which is correct. I then want to add the result of these three
answers, but the #N/A doesn't allow. I found a thread in this forum regarding
ISERROR and attempted to use it in my formula, but it still returns #N/A.
Any assistance would be appreciated. Here's my formula:
=IF(ISERROR(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))*(B14/60),"",(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33)))

iserror
 

Looks like you just need some additional parenthesis. Try this:

=IF(ISERROR((LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))*(B14/60)),"",LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))

You were taking the results of the ISERROR function (TRUE/FALSE) and
multiplying that by (B14/60). Now the (B14/60) is evaluated in the ISERROR
function.

HTH,
Elkar


"Dauphin" wrote:

I am using LOOKUP to obtain values from another sheet, this formula is
replicate 3 times. In most cases at least one of the three formulas will
return #N/A, which is correct. I then want to add the result of these three
answers, but the #N/A doesn't allow. I found a thread in this forum regarding
ISERROR and attempted to use it in my formula, but it still returns #N/A.
Any assistance would be appreciated. Here's my formula:
=IF(ISERROR(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))*(B14/60),"",(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33)))

iserror
 

Eklar, thanks for your help. When I enter your corrections it took care of
the #N/A error message and the cell is now blank, but I had to replace the ""
with a ZERO to get the results to add together. I think because the "" is
viewed as a text string and cannot be added. Anyway we got the problem
resolved. I've been struggling with this for hours and as soon as I found
this forum my problem was resolved within minutes. Have a great weekend!

"Elkar" wrote:

Looks like you just need some additional parenthesis. Try this:

=IF(ISERROR((LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))*(B14/60)),"",LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))

You were taking the results of the ISERROR function (TRUE/FALSE) and
multiplying that by (B14/60). Now the (B14/60) is evaluated in the ISERROR
function.

HTH,
Elkar


"Dauphin" wrote:

I am using LOOKUP to obtain values from another sheet, this formula is
replicate 3 times. In most cases at least one of the three formulas will
return #N/A, which is correct. I then want to add the result of these three
answers, but the #N/A doesn't allow. I found a thread in this forum regarding
ISERROR and attempted to use it in my formula, but it still returns #N/A.
Any assistance would be appreciated. Here's my formula:
=IF(ISERROR(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33))*(B14/60),"",(LOOKUP(D16,'Point Scale'!$G$4:$G$33,'Point
Scale'!$H$4:$H$33)))