I need to determine mean, median, mode, min, max and rank for a set of data
that exceeds a count of 255. Excel keeps locking up on me I think because
these functions are limited to arrays of 255 numbers or less.

Is there a way I can calculate these statistics on sets of data numbering in
the thousands?

On Sat, 21 Jun 2008 20:54:01 -0700, Brian Mc in StL <Brian Mc in
wrote:

I need to determine mean, median, mode, min, max and rank for a set of data
that exceeds a count of 255. Excel keeps locking up on me I think because
these functions are limited to arrays of 255 numbers or less.

Is there a way I can calculate these statistics on sets of data numbering in
the thousands?

More data would be useful to try to figure out your problem, and why your Excel
is "locking up" (whatever that means)

=MIN(A:A)
=AVERAGE(A:A)
=MODE(A:A)

works fine with values in column A.

etc.
--ron

Thanks for the response...When I use these functions on data in a column that
contains less than 255 data points they work fine. When there are more data
points (like 400 or 1000), Excel says that it is calculating but it never
finishes. I tried letting it be for an hour one time but it didn't budge.
Usually, however, the bar at the top of Windcws says Microsoft Excel is not
responding. I then have to force it to close and reopen.

I believe I read somwhere in HELP that these functions are limited to 255
data points but it seems like it should handle more than that relatively
small amount of data.

"Ron Rosenfeld" wrote:

On Sat, 21 Jun 2008 20:54:01 -0700, Brian Mc in StL <Brian Mc in
wrote:

I need to determine mean, median, mode, min, max and rank for a set of data
that exceeds a count of 255. Excel keeps locking up on me I think because
these functions are limited to arrays of 255 numbers or less.

Is there a way I can calculate these statistics on sets of data numbering in
the thousands?

More data would be useful to try to figure out your problem, and why your Excel
is "locking up" (whatever that means)

=MIN(A:A)
=AVERAGE(A:A)
=MODE(A:A)

works fine with values in column A.

etc.
--ron

The limitation is due to the function and not the range of points. For
example:

=AVERAGE(A1:A256)


will work just fine. However:

=AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A1 3,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25, A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A3 8,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50, A51,A52,A53,A54,A55,A56,A57,A58,A59,A60,A61,A62,A6 3,A64,A65,A66,A67,A68,A69,A70,A71,A72,A73,A74,A75, A76,A77,A78,A79,A80,A81,A82,A83,A84,A85,A86,A87,A8 8,A89,A90,A91,A92,A93,A94,A95,A96,A97,A98,A99,A100 ,A101,A102,A103,A104,A105,A106,A107,A108,A109,A110 ,A111,A112,A113,A114,A115,A116,A117,A118,A119,A120 ,A121,A122,A123,A124,A125,A126,A127,A128,A129,A130 ,A131,A132,A133,A134,A135,A136,A137,A138,A139,A140 ,A141,A142,A143,A144,A145,A146,A147,A148,A149,A150 ,A151,A152,A153,A154,A155,A156,A157,A158,A159,A160 ,A161,A162,A163,A164,A165,A166,A167,A168,A169,A170 ,A171,A172,A173,A174,A175,A176,A177,A178,A179,A180 ,A181,A182,A183,A184,A185,A186,A187,A188,A189,A190 ,A191,A192,A193,A194,A195,A196,A197,A198,A199,A200 ,A201,A202,A203,A204,A205,A206,A207,A208,A209,A210 ,A211,A212,A213,A214,A215,A216,A217,A218,A219,A220 ,A221,A222,A223,A224,A225,A226,A227,A228,A229,A230 ,A231,A232,A233,A234,A235,A236,A237,A238,A239,A240 ,A241,A242,A243,A244,A245,A246,A247,A248,A249,A250 ,A251,A252,A253,A254,A255,A256)

will not work.
--
Gary''s Student - gsnu200793


"Brian Mc in StL" wrote:

Thanks for the response...When I use these functions on data in a column that
contains less than 255 data points they work fine. When there are more data
points (like 400 or 1000), Excel says that it is calculating but it never
finishes. I tried letting it be for an hour one time but it didn't budge.
Usually, however, the bar at the top of Windcws says Microsoft Excel is not
responding. I then have to force it to close and reopen.

I believe I read somwhere in HELP that these functions are limited to 255
data points but it seems like it should handle more than that relatively
small amount of data.

"Ron Rosenfeld" wrote:

On Sat, 21 Jun 2008 20:54:01 -0700, Brian Mc in StL <Brian Mc in
wrote:

I need to determine mean, median, mode, min, max and rank for a set of data
that exceeds a count of 255. Excel keeps locking up on me I think because
these functions are limited to arrays of 255 numbers or less.

Is there a way I can calculate these statistics on sets of data numbering in
the thousands?

More data would be useful to try to figure out your problem, and why your Excel
is "locking up" (whatever that means)

=MIN(A:A)
=AVERAGE(A:A)
=MODE(A:A)

works fine with values in column A.

etc.
--ron

On Sat, 21 Jun 2008 21:25:01 -0700, Brian Mc in StL
wrote:

Thanks for the response...When I use these functions on data in a column that
contains less than 255 data points they work fine. When there are more data
points (like 400 or 1000), Excel says that it is calculating but it never
finishes. I tried letting it be for an hour one time but it didn't budge.
Usually, however, the bar at the top of Windcws says Microsoft Excel is not
responding. I then have to force it to close and reopen.

I believe I read somwhere in HELP that these functions are limited to 255
data points but it seems like it should handle more than that relatively
small amount of data.


It does.

If that were the case, then the formulas I posted in my initial response to you
would not work.

What was your result when you tried the formulas I posted?

There is likely some problem with how you have entered your formula, or a
problem with your data.

You really need to provide some specifics for assistance. What formulas are
you using? What does your data look like?
--ron

Just to confirm Ron's reply:
AVERAGE(A:A) and AVERAGE(A1:A1000) work
The problem lies elsewhere.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brian Mc in StL" <Brian Mc in wrote in
message ...
I need to determine mean, median, mode, min, max and rank for a set of data
that exceeds a count of 255. Excel keeps locking up on me I think because
these functions are limited to arrays of 255 numbers or less.

Is there a way I can calculate these statistics on sets of data numbering
in
the thousands?

Gary''s Student wrote...
The limitation is due to the function and not the range of points. *For
example:

=AVERAGE(A1:A256)

will work just fine. *However:

=AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A 13,A14,A15,A16,A17,A18,A19,A20,
A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A 33,A34,A35,A36,A37,A38,A39,A40,
A41,A42,A43,A44,A45,A46,A47,A48,A49,A50,A51,A52,A 53,A54,A55,A56,A57,A58,A59,A60,
A61,A62,A63,A64,A65,A66,A67,A68,A69,A70,A71,A72,A 73,A74,A75,A76,A77,A78,A79,A80,
...
A251,A252,A253,A254,A255,A256)

will not work.
...

Me, I prefer to believe OPs know what they're talking about rather
than that they're so stupid they can't distinguish SYNTAX ERRORS from
calculation errors.

Excel 2003 and prior won't accept entry of your formula BOTH because
there are too many arguments ( 30) and too many characters ( 1024).

The OP said the formulas he was using worked when there were fewer
than 256 data points. If there were 255 data points, he couldn't have
been anything like your formula because even for A1,...,A255, there'd
be too many arguments and too many characters.

OTOH, if the OP were using Excel 2007 (or 2008), then your formula
*WOULD* work.

So how about making an ATTEMPT to respond to the OP on the basis of
assuming the OP *DOES* understand what he's saying?

Brian Mc in StL wrote...
Thanks for the response...When I use these functions on data in a column that
contains less than 255 data points they work fine. *When there are more data
points (like 400 or 1000), Excel says that it is calculating but it never
finishes. *I tried letting it be for an hour one time but it didn't budge.. *
Usually, however, the bar at the top of Windcws says Microsoft Excel is not
responding. *I then have to force it to close and reopen.

There's got to be other formulas that are causing problems. For
example, if I enter 1 in A1, 2 in A2, select A1:A65536 and Edit Fill
Series, I get the row number in each cell in A:A. The following
formulas calculate without incident.

C1: =MIN(A:A) 1
C2: =MODE(A:A) #NUM!
C3: =MEDIAN(A:A) 32768.5
C4: =AVERAGE(A:A) 32768.5
C5: =MAX(A:A) 65536

I believe I read somwhere in HELP that these functions are limited to 255
data points but it seems like it should handle more than that relatively
small amount of data.
...

Not quite.

If you're using range arguments like A:A or C5:IT10004, Excel will
process all cells in these ranges (65,536 and 2,520,000 cells,
respectively). What Excel won't do is accept ENTRY of formulas with
more than 30 arguments (Excel 2003/2004 and prior) or 255 arguments
(Excel 2007/2008).

However, Excel *WILL* recalculate formulas with more arguments than
it'll accept on entry if those formulas originated in other
spreadsheets. For example, Lotus 123 happily accepts 40 function
arguments, and will save formulas with that many arguments in its
own .WK4 file format. Excel 2003 and prior will happily load such
files (well, it would before recent 'Security' packs) *AND*
*RECALCULATE* such formulas and even save them in .XLS file format.
It's Excel's formula parser that has the arbitrary limitation, not
Excel's calculation engine.

If you're not getting syntax errors when you try to enter your
formulas, then those formulas themselves aren't the problem.

What other formulas are in your spreadsheet? Are your 'data points'
numeric constants or are they formulas? If they're formulas, do any
include RAND() terms? If so, MEDIAN, MODE and RANK might have problems
with volatile data range values. BTW, what do your RANK formulas look
like? Using my simplistic A:A sample data above, if the RANK formulas
are anything like

B1: =RANK(A1,A:A)

then that's almost certainly your problem. If so, it'd be FAR MORE
EFFICIENT to include an additional column for original row number (as
values, not formulas), then sort on the data values column first and
the original row number column second. The sorted order would then be
the rank. Add a rank column containing the new row numbers (again as
values), then resort on original row number. Basically, Excel's sort
algoritm is a variation on QuickSort, so O(N*log(N)), but a single
RANK(A1,A:A) call is O(N), so N such formulas is an O(N^2) process,
which for large ranges would be MUCH SLOWER than O(N*log(N)).