MODE function limitation?

[pioniere]

Hello, I have a spreadsheet with columns holding around 3,000 values each. I would like to use the MODE function to determine the most frequently occurring value in each column. My research has told me that Excel 2003 limits the number of values for MODE to 30. Is this correct in all uses of MODE, or only if I actually place 30 cell references in the formula (i.e., =MODE(A2, A3, A4, etc.)

If I use a mode formula like:

=MODE(A2..A2650)

will it return a valid (reliable) value?

If not, how can I work around this?

Thanks in advance for helping this newbie!

 

[VoG]

Try

=MODE(A2:A2650)

 

[pioniere]

So can someone say for sure that the 30 item limitation does not apply to MODE when used in this form:

=MODE(A2:A2456)

Thanks!

 

[barry houdini]

Yes, that's right

MODE, like several other functions, e.g. SUM accepts up to 30 arguments. That's not the same as a limitation of 30 cells. A range, of any size, is a single argument, so with your example you are using only 1 of the 30 allowed. There's nothing to stop you using MODE like this with a mixture of ranges and actual values

=MODE(A1:A100,B1:B100,5,Z2:Z30,4)

 

[Aladin Akyurek]

So can someone say for sure that the 30 item limitation does not apply to MODE when used in this form:

=MODE(A2:A2456)

Thanks!

That's just one argument ( a reference argument ). On versions prior 2007, you can have 30 of them... For exemple:

=MODE(A2:A256,B2:B10,D2:F2,10,20,10)

 

[pioniere]

Thanks everyone for clarifying!