MAX formula

[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

[Bryan Hessey]

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.

You could also use Max(C:C) so that you don't forget when you exceed 3,000.

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]

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]

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.



added,
If that doesn't help, then remove all the data except for C3:C35 and the =Max cell and post the sheet here.

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

[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


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

[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