| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup folmula i/e n/a
I am using this formula to get the last value in the column
=LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
This work?
=IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)), 0,LOOKUP(9.99999999999999E+307,L7:L516)) gwbdirect wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have value’s and I want to add them up for a total and n/a won’t allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
FANTASTIC, THANKS FOR ALL YOUR HELP.
"JW" wrote: This work? =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)), 0,LOOKUP(9.99999999999999E+307,L7:L516)) gwbdirect wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
=if(isna(LOOKUP(9.99999999999999E+307,L7:L516),0,L OOKUP(9.99999999999999E+307,L7:L516))
"gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
Joel,
You left out a parenthesis before the first comma, should have two. Alan "Joel" wrote: =if(isna(LOOKUP(9.99999999999999E+307,L7:L516),0,L OOKUP(9.99999999999999E+307,L7:L516)) "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
=IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516))
Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
Alan: You should have double quotes around your zero. It is a number not a
string. "Alan" wrote: =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516)) Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
You are correct, at least my formula doesn't error.
"Joel" wrote: Alan: You should have double quotes around your zero. It is a number not a string. "Alan" wrote: =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516)) Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
You would get wrong results if you tried to used the results from your
formula in any arithmetic operation. "Alan" wrote: You are correct, at least my formula doesn't error. "Joel" wrote: Alan: You should have double quotes around your zero. It is a number not a string. "Alan" wrote: =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516)) Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
You can use the "0" string * 1 in another formula.
I appreciate you trying to set me straight, but please pay more attention to your own posts. Your first attempt at the formula errors out, and your correction of my formula, "You should have double quotes around your zero", also errors. "Joel" wrote: You would get wrong results if you tried to used the results from your formula in any arithmetic operation. "Alan" wrote: You are correct, at least my formula doesn't error. "Joel" wrote: Alan: You should have double quotes around your zero. It is a number not a string. "Alan" wrote: =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516)) Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup folmula i/e n/a
WORKS GREAT. THANKS FOR YOUR SUPPORT.
"Alan" wrote: =IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0 ",LOOKUP(9.99999999999999E+307,L7:L516)) Alan "gwbdirect" wrote: I am using this formula to get the last value in the column =LOOKUP(9.99999999999999E+307,L7:L516) The problem is when it has no value instead of 0 it gives me #n/a. This is a problem because I have different sheets that have values and I want to add them up for a total and n/a wont allow me to get a total. Is there a solution or another formula that would give me a 0 instead of n/a thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| I need help with an folmula for hunting / matching names and value | Excel Worksheet Functions | |||
Hybrid Mode
