#1   Report Post  
Old November 15th 08, 05:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 12
Default 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   Report Post  
Old November 15th 08, 05:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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   Report Post  
Old November 15th 08, 07:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old November 15th 08, 07:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old November 15th 08, 07:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old November 16th 08, 07:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to combine an IF Function with a lookup function to determine [email protected] Excel Worksheet Functions 1 December 5th 06 07:09 AM
lookup function B G Excel Discussion (Misc queries) 2 June 22nd 06 10:35 AM
lookup function Anh Excel Worksheet Functions 12 December 25th 05 10:55 PM
Pivot table doing a lookup without using the lookup function? NGASGELI Excel Discussion (Misc queries) 0 August 2nd 05 05:08 AM
Lookup Function andibevan Excel Worksheet Functions 1 October 28th 04 12:25 PM


All times are GMT +1. The time now is 06:41 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017