#1   Report Post  
Jonibenj
 
Posts: n/a
Default 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   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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   Report Post  
Jonibenj
 
Posts: n/a
Default


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   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Jonibenj
 
Posts: n/a
Default


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF formula? meris Excel Worksheet Functions 1 September 6th 05 07:14 AM
writing a formula for a colored value aaronwexler New Users to Excel 11 September 1st 05 03:11 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 10:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"