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