Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
Hi
I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
Use it like this
=IF(ISNA(your lookup formula),"",your lookup formula) ISNA will be true if lookup returns #N/A and you will get blank in your cell. If it is not #N/A then you get the result of your lookup. I did not understand what you mean by 1E10... Is it a string? If yes, then use "1E10". I pasted your formula but it was not accepted by Excel due to 1E10... "Walley" wrote: Hi I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
Try this:
=IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6,"") 1E100 and 1E+100 mean the same thing in Excel. It's scientific notation for a very large number, 1 followed by 100 zeros. The pedantic method is to use 9.99999999999999E+307 but I think this is even more confusing to most users. How many 9s do I have to enter??????? You may also see something like these being used: 10^100 or 99^99. LOOKUP(10^100;C6:C25) Those also generate a very large number but those have to calculate while 1E100 is a constant. The way it works: If all the numeric values in the referenced range are less than the lookup_value the formula returns the *last* numeric value in the range. To ensure we get the correct result we use a lookup_value that is guaranteed to be greater than any value in the range so we use an arbitrary gigantic number like 1E100. You could also use something like this if it helps to understand better: LOOKUP(MAX(C6:C25)+1;C6:C25) -- Biff Microsoft Excel MVP "Walley" wrote in message ... Hi I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
I pasted your formula but it was not accepted by Excel due to 1E10...
It was probably not accepted because the OP's formula uses semicolons as separators and your regional settings probably use a comma. The separators are a regional setting. -- Biff Microsoft Excel MVP "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Use it like this =IF(ISNA(your lookup formula),"",your lookup formula) ISNA will be true if lookup returns #N/A and you will get blank in your cell. If it is not #N/A then you get the result of your lookup. I did not understand what you mean by 1E10... Is it a string? If yes, then use "1E10". I pasted your formula but it was not accepted by Excel due to 1E10... "Walley" wrote: Hi I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
Ooops!
=IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6,"") Change that comma to a semicolon: =IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6;"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6,"") 1E100 and 1E+100 mean the same thing in Excel. It's scientific notation for a very large number, 1 followed by 100 zeros. The pedantic method is to use 9.99999999999999E+307 but I think this is even more confusing to most users. How many 9s do I have to enter??????? You may also see something like these being used: 10^100 or 99^99. LOOKUP(10^100;C6:C25) Those also generate a very large number but those have to calculate while 1E100 is a constant. The way it works: If all the numeric values in the referenced range are less than the lookup_value the formula returns the *last* numeric value in the range. To ensure we get the correct result we use a lookup_value that is guaranteed to be greater than any value in the range so we use an arbitrary gigantic number like 1E100. You could also use something like this if it helps to understand better: LOOKUP(MAX(C6:C25)+1;C6:C25) -- Biff Microsoft Excel MVP "Walley" wrote in message ... Hi I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function
Thanks for the information... That must have been the reason...
"T. Valko" wrote: I pasted your formula but it was not accepted by Excel due to 1E10... It was probably not accepted because the OP's formula uses semicolons as separators and your regional settings probably use a comma. The separators are a regional setting. -- Biff Microsoft Excel MVP "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Use it like this =IF(ISNA(your lookup formula),"",your lookup formula) ISNA will be true if lookup returns #N/A and you will get blank in your cell. If it is not #N/A then you get the result of your lookup. I did not understand what you mean by 1E10... Is it a string? If yes, then use "1E10". I pasted your formula but it was not accepted by Excel due to 1E10... "Walley" wrote: Hi I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which work if there is value in C6:C25, but if there is not any value in C6:C25, it return the "#N/A", how can I make cell G10 to not show "#N/A", if there is not any numbers in C6:C25. Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean. Thank in advance for help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
lookup function | Excel Discussion (Misc queries) | |||
lookup function | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Lookup Function | Excel Worksheet Functions |