Excel 2010 PL does not calculate the formula!

[daro]

I have the formula:
LOOKUP(MAX(H14:H27);H14:H27;A14:A27)& "- the most rear"

and everything is fine.

But if I change it to:
LOOKUP(MIN(H14:H27);H14:H27;A14:A27)& "- the smallest rear"

to get a #N/A error.
I checked the exact format and values of all cells, are in order. I do
not know what's next?!

Daro

 

[joeu2004]

I have the formula:
LOOKUP(MAX(H14:H27);H14:H27;A14:A27)& "- the most rear"
and everything is fine.

But if I change it to:
LOOKUP(MIN(H14:H27);H14:H27;A14:A27)& "- the smallest rear"
to get a #N/A error.

Works fine when I do it (in XL2003). Try this simple test. Start a
new instance of Excel. Enter 1,2,3,4,5 into A1:A5, and enter
6,7,8,9,10 into B6:B10. Then enter LOOKUP(MAX(A1:A5),A1:A5,B6:B10)
and LOOKUP(MIN(A1:A5),A1:A5,B6:B10).
(Note: You seem to use semicolons where I use commas.)

Do they both work?

If yes, there is something about H14:H27 or the formulas or the Excel
file that you are not telling us. I suggest that you upload an Excel
file that duplicates your problem to http://www.mediafire.com, and
post the URL in a response.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[joeu2004]

PS....

I have the formula:
LOOKUP(MAX(H14:H27);H14:H27;A14:A27)& "- the most rear"
and everything is fine.

But if I change it to:
LOOKUP(MIN(H14:H27);H14:H27;A14:A27)& "- the smallest rear"
to get a #N/A error.

Some common mistakes that exhibit those symptoms:

1. H14:H27 is not really in ascending order, despite your claim to the
contrary.

2. H14:H27 contains (some) numeric text, not actual numbers. Note
that "2" is not the same as 2.

BTW, I don't know why anyone would write those expressions, except for
testing purposes. LOOKUP/MIN should always return A14; so write
simply =A14. LOOKUP/MAX should always return A27; so write =A27.

If A14:A27 is actuallyl a named ranged and you want expressions to
return the first and last, then INDEX(myRange,1) returns the contents
of A14. You can use INDEX(myRange,COUNTA(myRange)) to return the
contents of A27 if there are no empty cells in myRange.

 

[daro]

W dniu 2010-11-15 17:16, joeu2004 pisze:

If yes, there is something about H14:H27 or the formulas or the Excel
file that you are not telling us. I suggest that you upload an Excel
file that duplicates your problem tohttp://www.mediafire.com, and
post the URL in a response.

I created a new excel file, I inserted the hand of the same data and
created a formula LOOKUP(MIN(H14:H27);H14:H27;A14:A27)& "- the smallest
rear". Working properly. This means that something is wrong with the
file. May have been damaged.

Thanks for your help.
Daro