Excluding cells with no value in calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to calculate a value based on a VLOOKUP, but one of the cells (outside
the VLOOKUP table) may be blank. If that cell is blank, I want zero for an
answer. My current formula is:
=(VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))

Cell E2 is a percentage
Cell C2 is a quote number

J K
L
GP Greater Than or Equal To… But Less Than… Points
-100% 0% 0.0
0% 21% 0.5
21% 31% 1.0
31% 40% 1.5
40% 101% 2.0
 
One way

=(C2<>"")*(VLOOKUP(E2,$J$2:$L$6,3,0)+IF(ISNUMBER(C2),0.5,0))


Regards,

Peo Sjoblom
 
Teri wrote...
I still get #N/A as a result.

"Peo Sjoblom" wrote: ....

Peo may have erred by using a 4th argument to VLOOKUP. Given the table
above, it's unlikely you want exact matching. Try

=IF(C2="","",VLOOKUP(E2,$J$2:$L$6,3)+ISNUMBER(C2),0.5,0))
 
I misunderstood, I thought that C2 was empty, so you just want to get rid of
the N/A error from the VLOOKUP
because the numeric value is not within the range,

IF(ISNA(VLOOKUP(E2,$J$2:$L$6,3)),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))

Regards,

Peo Sjoblom
 
Now I'm getting .5 in the cell even when there's nothing in the other cells.
This is so frustrating. Can you figure it out?
 
I copied this right into my spreadsheet and got an error message. Is there a
( missing somewhere?
 
Which cell is it that can be blank, if it is C2 then you can use my first
formula after removing ,0 in the VLOOKUP formula, if you want to test against
both C2 and an error from the VLOOKUP you can use


=IF(OR(C2="",ISNA(VLOOKUP(E2,$J$2:$L$6,3))),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom
 
FANTASTIC! Thank you SO MUCH for your help!

Peo Sjoblom said:
Which cell is it that can be blank, if it is C2 then you can use my first
formula after removing ,0 in the VLOOKUP formula, if you want to test against
both C2 and an error from the VLOOKUP you can use


=IF(OR(C2="",ISNA(VLOOKUP(E2,$J$2:$L$6,3))),0,VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom
 
Teri said:
I copied this right into my spreadsheet and got an error message.
Is there a ( missing somewhere?

"Harlan Grove" wrote: ....

No, there's a missing IF call. Make that

=IF(C2="","",VLOOKUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))
 

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

Back
Top