MAX formula

J

Jonibenj

I've entered the following formula to find the largest value in a list:

MAX(C3:C3000)

So far, there are values in C3 to C35 - the formula is allowing for
expansion. The only result I can get is zero. Any suggestions?

Jonathan
 
J

Jonibenj

Dear Bryan,

Yes, the values are numeric. The cells are under the numbe
formatting.
I don't understand what you mean by a 'helper column'.

Jonatha
 
B

Bryan Hessey

Jonathan

A 'helper' column is a 'spare' column used for testing etc, if you hav
nothing in column D that would be useful.

In this case it would show whether your cells in C3 to C35 have
'numeric' problem that the =Max doesn't like.
 
D

Dave Peterson

Just because the format is Number doesn't mean that the values are numeric.
They can still be text.

One way to convert Text Numbers to Number Numbers is to:
select an empty cell
edit|copy
select your range (all of column C???)
edit|Paste special|check add

If that doesn't make your =max() function work, any chance you're copying your
data from a web site and getting extra characters (especially the HTML
non-breaking space character) in the cell?

If yes, then you may want to try David McRitchie's routine to clean that stuff
up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
J

Jonibenj

Thanks for the tip, Dave, it worked! I had originally entered the
values as text objects, because I wanted a zero in front, eg, 0147.
Thinking that the MAX formula might not like these zeros, I reformatted
the column as numbers, and took off the zeros. However, the numbers
were still actually text numbers, like you suggested. We live and we
learn, don't we? :) :)

Jonathan
 
Top