Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |