MAX function problem

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

When I want to find the highest number in a range the result returns the
first 8 digits only (00000000)

=MAX(A1:A9) returned 00000000
correct answer should be 0000000010092

0000000010016
0000000010023
0000000010030
0000000010047
0000000001005
0000000001006
0000000010078
0000000010085
0000000010092

I have tried custom format but without much difference.
Any thoughts what is causing this?
 
Hi
sounds like your numbers are actually stored as 'Text'.
What does the formula
=ISNUMBER(A1)
return. I would suspect 'FALSE'

Try the following:
- change the format to a custom format
- copy an empty cell
- select your column A
- goto 'Edit - Paste Special' and choose 'Add'

Now try again
 
Pat

You have entered the numbers as text.

Instead try this array formula:

=MAX(VALUE(A1:A9))

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.

The formula will return 10092.
 
Pat,

Try this array formula(with CTRL+SHIFT+ENTER):

=MAX(--A1:A9)

or

=TEXT(MAX(--A1:A9),"0000000000000")



--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
=MAX(VALUE(A1:A9))

This has given the result I needed.

Thank you gentlemen for helping out.

regards
Pat
 
Pat wrote...
When I want to find the highest number in a range the result
returns the first 8 digits only (00000000)

=MAX(A1:A9) returned 00000000
correct answer should be 0000000010092
Click to expand...
...

You've already received a working answer, but here's an alternativ
that returns the lexical 'maximum' (text which would sort first i
descending order).

=LOOKUP(2,1/(COUNTIF(Rng,">"&Rng)=0),Rng
 
hgrove > said:
Pat wrote...
..

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)
Click to expand...

With LOOKUP() Rng must be in ascending order, which doesn't seem
to be the case here.
For a solution, which works for a sorted or unsorted range try
this array formula instead:

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

LeoH
 
Leo Heuser wrote...
"hgrove >" <<[email protected]> skrev ...

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

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))
Click to expand...

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.
 
hgrove > said:
Leo Heuser wrote...

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.
....
....
It seems you either don't read these newsgroups much any more, or
perhaps don't understand much of what you read
Click to expand...

Just another potion of your foul smelling burps. Of course I tested it, but
as you can read below, I had another understanding of the word "highest",
than you had.
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.
Click to expand...

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.

LeoH
 
Leo Heuser said:
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.
Click to expand...
Are you telling me, that HIGHEST *always* means LAST in this context,
that it can *never* mean LARGEST?
Click to expand...
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.
Click to expand...

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 > said:
Leo Heuser wrote...
"hgrove >" <<[email protected]> skrev ..

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

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))
Click to expand...

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.
Click to expand...
 
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.
Click to expand...

Your interpretation is as deficient as your cognitive processe
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' (tex
which would sort first in descending order)."

I'll admit that "lexical 'maximum'" is tortured usage, but lexical doe
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 we
pages about 'lexical ordering'. You can use Google to find them i
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 firs
in descending order'. I could have written 'text which would sort las
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 pehap
'in'?


Then there was my follow-up to your initial response to me in which
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 followin
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 D1:D10

=1/C1:C10

Then you can enter the following formula in D12.

=LOOKUP(2,D1:D10,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 fe
times in case it does appear ordered.

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

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

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

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

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

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


You failed to understand my description *AND* my formula in my initia
response. And it's *YOU* who have screwed up in terms of understandin
the language (which you may have some excuse not to understand, bu
would beg the question why you continue to participate in Englis
language newsgroups), understanding the formula (which you really hav
no excuses at all for failing to understand, though this failure coul
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?
 
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.
Click to expand...

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.
 
Sandy Mann wrote...
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)
Click to expand...


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
 
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)
Click to expand...

This would eliminate text evaluating to "", not that such text i
likely to be 'greater than' any other text in Rng. However, COUNTI
with the &"*" at the end of the criteria would discard blank cells i
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
 
Frank Kabel wrote...
..

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 "".
Click to expand...
I'd be more explicit.

=LOOKUP(2,1/(COUNTIF(Rng,">"&Rng&"*")=-ISBLANK(Rng)),Rng)
Click to expand...

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
 
Back
Top