MAX formula
 

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


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466699

Are the cells C3 to C35 'numeric' values, ie, does the number show on
the right side or left side of the cell?

Try a helper column which has =value(C3) (down to C35) and test the Max
from there, it should work if your entries are numeric.



Jonibenj Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=466699

Dear Bryan,

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

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466699

Jonathan

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

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



Jonibenj Wrote:
Dear Bryan,

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

Jonathan


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=466699

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


Jonibenj wrote:

Dear Bryan,

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

Jonathan

--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466699

--

Dave Peterson

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


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466699