MAX function problem

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

-----Original Message-----
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?


.

Pat

You have entered the numbers as text.

Instead try this array formula:

=MAX(VALUE(A1:A9))

The formula must be entered with ShiftCtrlEnter,
also if edited later.

The formula will return 10092.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pat" skrev i en meddelelse
...
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?

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


"Leo Heuser" wrote in message
...
Pat

You have entered the numbers as text.

Instead try this array formula:

=MAX(VALUE(A1:A9))

The formula must be entered with ShiftCtrlEnter,
also if edited later.

The formula will return 10092.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pat" skrev i en meddelelse
...
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?

You're welcome, Pat, and thanks for the feedback :-)

Regards
LeoH


"Pat" skrev i en meddelelse
...
=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
...

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)


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

"hgrove " skrev i en meddelelse
...
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
..

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.
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 " 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/

"hgrove " skrev i en meddelelse
...
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.
....
....
It seems you either don't read these newsgroups much any more, or
perhaps don't understand much of what you read

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.

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