MAX function problem

"Leo Heuser" wrote...
....
as you can read below, I had another understanding of the word "highest",
than you had.
....
When the OP asked for the HIGHEST number in a range, I believed he was
asking for the LARGEST number and not the LAST number in the range.
I may be wrong, but I have seen HIGHEST used in this meaning countless
times, but since English isn't my first language, I may have made a false
assumption.

Are you telling me, that HIGHEST *always* means LAST in this context,
that it can *never* mean LARGEST?

If so, I'm sorry to have corrected you. If not, you could at least have
given me the benefit of doubt, instead of sputtering your infantile
accusations.

The persistence of your stupidity can at times be astounding.

Reread my first response. Reread my last follow-up to you. Consider using
Babelfish to translate my English into some other language you'd find easier
to read.

I've consistently used the term LEXICAL MAXIMUM, which I defined as "text
which would sort first in descending order", such as the Y in my most recent
follow-up to you.

I guess I need to put this in very simple terms which seems to be what you
need. With A1:A10 containing

D
E
W
Y
A
E
B
X
M
M

(so D in A1, Y in A4 and M in both A9 and A10), the expression

1/(COUNTIF(A1:A10,""&A1:A10)=0)

returns the array result

#DIV/0!
#DIV/0!
#DIV/0!
1
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!

LOOKUP, MATCH(.,.,1), VLOOKUP(.,.,.,1) and HLOOKUP(.,.,.,1) all seem to
ignore any values in the applicable column or row of their 2nd arg that are
of different type than their 1st args. So using LOOKUP to find 2 in the
array result above finds the 1 in the 4th row, and so returns the
corresponding entry in its 3rd argument.

The real magic (might as well be in your case) is found in the COUNTIF call,
which returns 0 for the entry in A1:A10 for which there are no 'larger'
elements. In this case, 'large' means what you claimed you believe it means.

This is the critical place in which LARGEST == LAST also means LARGEST ==
GREATEST == first when sorting in descending order. Amazing what a little
cleverness will get you. Sad what a lack of cleverness makes soooooo hard to
grasp.

You didn't test this originally. You're lying to cover you butt. Either that
or you're imcompetent to test something like this. Either way, you're
persisting in being an idiot. How much longer?

You are a pathetic clown, who even haven't got the
minimum of common decency to answer my VERY simple
question about HIGHEST. Probably because you can't bear
to admit, that my interpretation of the word is valid.

Harlan,

I can't get your formula to work, (in XL 97), on the OP's original data. It
does return 0000000010092 but only because that is the last item in the
list, if I swap the last item with another list item it still returns the
last item in the list even although there is a *larger* text number above
it.

If the OP's data is entered without the leading 0's then yes it does return
the largest text number unless there is a blank cell *after* the largest
number whereupon it returns 0. I may be wrong, but it seems to me that
COUNTIF is not looking past the first character unless Excel can recognise
it as a number even although it is text. COUNTIF(A1:A9,""&A1:A9) is
therefore returning {0;0;0;0;0;0;0;0;0}and thus the last entry in the list
is returned by the formula

Try entering:
09
08
07
06
05
04
0
02
01

in A1 :A9 Your formula =LOOKUP(2,1/(COUNTIF(A1:A9,""&A1:A9)=0),A1:A9) will
now return 01 or any other last entry in the list.

However, it is not as simple as that. If you enter the list:

Alan
Daniel
Carol
Charlie
Bert
David
Andrew
Able

then your formula will return *David* as it should. If you change Daniel to
Davis then it returns Davis, just as it should. Again if there is an empty
cell after the *largest* entry it returns 0 regardless. So it seems to me
that COUNTIF looks at all the letters in a *word* but will not look past the
first zero in a text number.

Or am I missing something?

Regards

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"hgrove " wrote in message
...
Leo Heuser wrote...
"hgrove " skrev
..
You've already received a working answer, but here's an
alternative that returns the lexical 'maximum' (text which
would sort first in descending order).

=LOOKUP(2,1/(COUNTIF(Rng,""&Rng)=0),Rng)

With LOOKUP() Rng must be in ascending order, which doesn't
seem to be the case here.

Obviously you didn't test this. Why not? Too lazy? Too stupid to
consider doing so? Too sure of your own encyclopedic knowledge of Excel
worksheet functions? If the last, you need some remedial study on how
LOOKUP *ACTUALLY* works as opposed to how online help says it works.

Given the following in A1:A10, which I've named Rng,

D
E
W
Y
A
E
B
X
M
M

my formula returns Y. Select A1:A10, type the formula

=CHAR(65+26*RAND())

and press [Ctrl]+[Enter]. Now repeatedly press [F9] and note the return
values from my formula. Do you see *ANY* instances in which the formula
doesn't return the lexically last letter?

It seems you either don't read these newsgroups much any more, or
perhaps don't understand much of what you read. You should have seen
Aladin's approach to finding the row number of the last cell containing
a number in a column,

=MATCH(9.99999999999999E307,A:A)

The reason this works is due to how Excel's MATCH function works when
called to perform approximate matching (find largest value in 2nd arg
= 1st arg). It'd start by trying to bracket its 1st arg value in its
2nd arg, but if the 1st arg is larger than any value in its 2nd arg, it
returns the index of the last number.

LOOKUP works the same way, and I believe many have used the idiom

=LOOKUP(9.99999999999999E307,A:A)

to return the value of the last numeric value in col A.

It's quite remarkable that someone who believes he knows Excel as well
as you must believe you do is ingorant of this. Worse, you can't be
bothered to test it before posting.

If you can find errors in my postings, fine - point 'em out. If, on the
other hand, you feel the urge to get pedantic but make erroneous claims
of mistakes in my postings, you can expect I'll respond in this fashion
- illustrating the full idiocy of your, er, contribution.

For a solution, which works for a sorted or unsorted range try
this array formula instead:

=INDEX(Rng,MATCH("*"&MAX(Rng+0),Rng,0))

If Rng contained only numbers and numeric strings, why would anyone
with the slightest sense (so apparently not you) use this formula
rather than =MAX(--Rng) ? If Rng contained some nonnumeric text, your
wonderful formula could return #VALUE!.

You don't need to thank me for my feedback.


---
Message posted from http://www.ExcelForum.com/

Leo Heuser wrote...
You are a pathetic clown, who even haven't got the minimum of
common decency to answer my VERY simple question about
HIGHEST. Probably because you can't bear to admit, that my
interpretation of the word is valid.

Your interpretation is as deficient as your cognitive processes
generally in this branch of the thread.

How do you interpret this from my initial response in this thread:

"...here's an alternative that returns the lexical 'maximum' (text
which would sort first in descending order)."

I'll admit that "lexical 'maximum'" is tortured usage, but lexical does
have a dictionary meaning.

http://www.yourdictionary.com/ahd/l/l0143800.html

I'll assume you don't need an English definition of 'maximum'. If not,
I'll be happy to provide you one.

Now maximum implies some sort of ordering, and there are lots of web
pages about 'lexical ordering'. You can use Google to find them if
you're interested. Heck, there are even hits for 'lexical maximum',
such as

http://www.inro.ca/enif/doc/using/config_attr.html

Is that web page too complicated for you?

Then there's my parenthetical definition, 'text which would sort first
in descending order'. I could have written 'text which would sort last
in ascending order'. Would that have helped you?

Is this really unclear? If so, which word(s) is(are) unclear - 'text',
'which', 'would', 'sort', 'first', 'descending', 'order', or pehaps
'in'?


Then there was my follow-up to your initial response to me in which I
proposed filling a range with the formula

=CHAR(65+26*RAND())

Why don't you try doing so in A1:A10? Then you can enter the following
array formula in B1:B10

=COUNTIF(A1:A10,""&A1:A10)

as well as the following array formula in C1:C10

=B1:B10=0

and the following array formula in D110

=1/C1:C10

Then you can enter the following formula in D12.

=LOOKUP(2,D110,A1:A10)

Finally, you can enter the following formula in A12.

=LOOKUP(2,1/(COUNTIF(A1:A10,""&A1:A10)=0),A1:A10)

Column A isn't sorted except in rare fortuitous cases. Press [F9] a few
times in case it does appear ordered.

Column B will show 0 (zero) corresponding to each instance of the
'lexical maximum' in column A, the text string that would sort first in
descending order or last in ascending order. Is this still simple enough
for you to understand? I'd guess this is the term you're struggling
with, and I could help you break through the rather thick walls of
ignorance that seem to be afflicting you.

Column C will show TRUE corresponding to each zero in column B, so
corresponding to each instance of the 'lexical maximum' in column A,
and FALSE corresponding to any other values in column B, so to values
other than the 'lexical maximum' in column A. Are you understanding
this so far?

Column D will show 1 (one) corresponding to each TRUE in column C, so
corresponding to each 0 (zero) in column B, so to each instance of the
'lexical maximum' in column A, and #DIV/0! corresponding to the FALSE
values in column C, so to nonzero values in column B, so to values
other than the 'lexical maximum' in column A. Is this simple enough for
you? I could try to use smaller English words.

The LOOKUP formula in D12 will then match the last instance of 1 in
D110. Do you need an explanation of why this is so?

If not, then the formula in A12 is effectively the same as the formula
in D12 except that its 2nd argument has been replaced with an
expression returning the same array result as D110 but referring
directly to A1:A10. Do you understand this?

Maybe this level of decomposition may finally allow you to figure out
what my formula does, but I won't hold my breath.


You failed to understand my description *AND* my formula in my initial
response. And it's *YOU* who have screwed up in terms of understanding
the language (which you may have some excuse not to understand, but
would beg the question why you continue to participate in English
language newsgroups), understanding the formula (which you really have
no excuses at all for failing to understand, though this failure could
be explained by your own pigheadedness), testing the formula (you're
either lying about testing it or incompetent to do so), and failing to
understand my follow-ups (there's more than just the well-deserved
insults in them). I haven't screwed up except in believing there may be
some small hope you can figure this out. For that I'll admit I could be
dead wrong - you may not be able to figure this out - and if so I
appologize for assuming you're smarter than you are.

So you're going to continue being an idiot, are you?


---
Message posted from http://www.ExcelForum.com/

Sandy Mann wrote...
I can't get your formula to work, (in XL 97), on the OP's original
data. It does return 0000000010092 but only because that is
the last item in the list, if I swap the last item with another list
item it still returns the last item in the list even although there
is
a *larger* text number above it.

I didn't test the formula with the OP's data, but I have now. I screwed
up by failing to recognize that COUNTIF would treat its 2nd argument as
numeric comparisons. You'd need to change my formula to

=LOOKUP(2,1/(COUNTIF(Rng,""&Rng&"*")=0),Rng)

So, if I augment the OP's data so,

0000000010016
0000000010023
0000000010030
0000000010047
0000000001005
0000000001006
0000000010078
0000000010085
0000000010092
0000000010016
0000000010023
0000000010030
0000000010047
0000000001005
0000000001006
0000000010078
0000000010085

and name the range containing this Rng, the formula above does return
0000000010092.

At this point, I need to appologize to Leo Heuser for calling him a
liar. If he tested with the OP's data, my formula would have failed,
but for reasons other than those he believed. On the other hand, you
did a very good job of explaining the problem.


---
Message posted from http://www.ExcelForum.com/

Sandy Mann wrote...
I can't get your formula to work, (in XL 97), on the OP's original
data. It does return 0000000010092 but only because that is
the last item in the list, if I swap the last item with another list
item it still returns the last item in the list even although there
is a *larger* text number above it.

I didn't test the formula with the OP's data, but I have now. I
screwed up by failing to recognize that COUNTIF would treat its 2nd
argument as numeric comparisons. You'd need to change my formula to

=LOOKUP(2,1/(COUNTIF(Rng,""&Rng&"*")=0),Rng)


Hi Harlan, Sandy Mann
I would add the following to ensure that this formula also work with
blank cells within rng:
=LOOKUP(2,1/((COUNTIF(Rng,""&Rng&"*")=0)*(Rng"")),Rng)


Frank

Thank you Harlan,

........... On the other hand, you
did a very good job of explaining the problem

High praise indeed, I will have to frame that one g

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Frank Kabel wrote...
...
I would add the following to ensure that this formula also work
with blank cells within rng:
=LOOKUP(2,1/((COUNTIF(Rng,""&Rng&"*")=0)*(Rng"")),Rng)

This would eliminate text evaluating to "", not that such text is
likely to be 'greater than' any other text in Rng. However, COUNTIF
with the &"*" at the end of the criteria would discard blank cells in
Rng as long as at least one of them evaluates to text other than "".

I'd be more explicit.

=LOOKUP(2,1/(COUNTIF(Rng,""&Rng&"*")=-ISBLANK(Rng)),Rng)


---
Message posted from http://www.ExcelForum.com/

Frank Kabel wrote...
..
I would add the following to ensure that this formula also work
with blank cells within rng:
=LOOKUP(2,1/((COUNTIF(Rng,""&Rng&"*")=0)*(Rng"")),Rng)

This would eliminate text evaluating to "", not that such text is
likely to be 'greater than' any other text in Rng. However, COUNTIF
with the &"*" at the end of the criteria would discard blank cells in
Rng as long as at least one of them evaluates to text other than "".

I'd be more explicit.

=LOOKUP(2,1/(COUNTIF(Rng,""&Rng&"*")=-ISBLANK(Rng)),Rng)

Aggreed. This would return #NA in case of an empty range.
Frank

I have realized, that life is too short to be spent on
egomaniacs like you, who apparently only thrive, if
they can hit other people with insolence and tons of
negative and destructive energy.
I simply won't waste more of my time holding a
discussion in such a spiteful and antisocial ambience.

LeoH