Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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)) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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)) |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
"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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions | |||
Help adding text values | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Automatic copying data excluding blank cells | Excel Worksheet Functions | |||
excluding cells from the formula when empty | Excel Worksheet Functions |