How do you average more than 30 time points in Excel

[Guest]

Trying to get the average of over one hunded times, in hours and minutes
format. Excel says that the Average function will not work for over thirty
data points. It works for other spreadsheets, where I have fewer time points.
How do you average more than 30 data points?

 

[Harlan Grove]

LoriG wrote...

Trying to get the average of over one hunded times, in hours and minutes
format. Excel says that the Average function will not work for over thirty
data points. It works for other spreadsheets, where I have fewer time points.
How do you average more than 30 data points?

You don't feed them separately to AVERAGE. If all your data points are
in adjacent cells, e.g., B5:B104, use the range reference as a single
argument to AVERAGE, like so.

=AVERAGE(B5:B104)

If your data points are in nonadjacent cells in the same worksheet,
e.g., every other row in column B from cell B5 to cell B203, use
multiple area ranges, like so.

=AVERAGE((B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,
B35,B37,B39,B41,B43,B45,B47,B49,B51,B53,B55,B57,B59,B61,B63,B65,B67,
B69,B71,B73,B75,B77,B79,B81,B83,B85,B87,B89,B91,B93,B95,B97,B99,B101,
B103,B105,B107,B109,B111,B113,B115,B117,B119,B121,B123,B125,B127,
B129,B131,B133,B135,B137,B139,B141,B143,B145,B147,B149,B151,B153,
B155,B157,B159,B161,B163,B165,B167,B169,B171,B173,B175,B177,B179,
B181,B183,B185,B187,B189,B191,B193,B195,B197,B199,B201,B203))

The two sets of parentheses are required. The inner set makes it a
multiple area range reference.

If your data points are all over the place, there's always nested sums
divided by sum of corresonding counts.

=SUM(SUM(..),SUM(..),..,SUM(..))/SUM(COUNT(..),COUNT(..),..,COUNT(..))

 

[John Michl]

I've never heard of a 30 data point limit. I just filled column A with
numbers and placed =Average(A1:A65535) in cell A65536 and it worked
fine. Where did you see information that states you can't do this?
Did you try?

- John

 

[Guest]

If data is column A, rows 1 to 400,

=AVERAGE(A1:A400)

Excel's limit is 30 arguments not points so in the example above there is
just one argument (range).

HTH

 

[John Michl]

Harlan, I just saw your post. Now the question makes more sense to me.
In response, I try to avoid, if at all possible, scattering data all
over the place. In this case, it makes the formula very difficult to
follow and trouble shoot.

- John