Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default How do you average more than 30 time points in Excel

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default How do you average more than 30 time points in Excel

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,B5 9,B61,B63,B65,B67,
B69,B71,B73,B75,B77,B79,B81,B83,B85,B87,B89,B91,B9 3,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(..))

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 81
Default How do you average more than 30 time points in Excel

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



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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default How do you average more than 30 time points in Excel

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

"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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 81
Default How do you average more than 30 time points in Excel

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


John Michl wrote:
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



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?


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
excel should let you vary error bars for individual points CRC Charts and Charting in Excel 4 July 5th 06 11:52 AM
time sheet drop down lists Steve Excel Discussion (Misc queries) 12 March 18th 06 11:30 PM
How do I enter a time in excel so it does not print as 1:00:00 PM Geet New Users to Excel 1 February 8th 06 11:29 PM
Excel Time Manipulation BFiedler Excel Discussion (Misc queries) 0 September 15th 05 01:15 AM
Accumulate weekly time to total time in Excel. delve Excel Discussion (Misc queries) 0 May 4th 05 08:14 PM


All times are GMT +1. The time now is 12:40 AM.

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"