Excluding cells with no value in calculation
 

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(C 2),0.5,0))


Regards,

Peo Sjoblom

"Teri" wrote:

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

I still get #N/A as a result.

"Peo Sjoblom" wrote:

One way

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


Regards,

Peo Sjoblom

"Teri" wrote:

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

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

"Peo Sjoblom" wrote:
....
=(C2<"")*(VLOOKUP(E2,$J$2:$L$6,3,0)+IF(ISNUMBER (C2),0.5,0))
....
"Teri" wrote:
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 ))
....
________J_______ ________K_______ __L__
GP Greater Than But Less Than... Points
or Equal To...
-100% 0% 0.0
0% 21% 0.5
21% 31% 1.0
31% 40% 1.5
40% 101% 2.0

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

"Teri" wrote:

I still get #N/A as a result.

"Peo Sjoblom" wrote:

One way

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


Regards,

Peo Sjoblom

"Teri" wrote:

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

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?

"Peo Sjoblom" wrote:

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

"Teri" wrote:

I still get #N/A as a result.

"Peo Sjoblom" wrote:

One way

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


Regards,

Peo Sjoblom

"Teri" wrote:

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

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

"Harlan Grove" wrote:

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

"Peo Sjoblom" wrote:
....
=(C2<"")*(VLOOKUP(E2,$J$2:$L$6,3,0)+IF(ISNUMBER (C2),0.5,0))
....
"Teri" wrote:
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 ))
....
________J_______ ________K_______ __L__
GP Greater Than But Less Than... Points
or Equal To...
-100% 0% 0.0
0% 21% 0.5
21% 31% 1.0
31% 40% 1.5
40% 101% 2.0

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))

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,VLOO KUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom
"Teri" wrote:

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?

"Peo Sjoblom" wrote:

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

"Teri" wrote:

I still get #N/A as a result.

"Peo Sjoblom" wrote:

One way

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


Regards,

Peo Sjoblom

"Teri" wrote:

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

FANTASTIC! Thank you SO MUCH for your help!

"Peo Sjoblom" wrote:

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,VLOO KUP(E2,$J$2:$L$6,3)+IF(ISNUMBER(C2),0.5,0))




Regards,

Peo Sjoblom
"Teri" wrote:

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?

"Peo Sjoblom" wrote:

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

"Teri" wrote:

I still get #N/A as a result.

"Peo Sjoblom" wrote:

One way

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


Regards,

Peo Sjoblom

"Teri" wrote:

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

"Teri" wrote...
I copied this right into my spreadsheet and got an error message.
Is there a ( missing somewhere?

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

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

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