Average Functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dusty - 21 Sep 2007 18:45 GMT

Howdy Techie Folks,
I use Excel for a variety of functions, one of which is keeping track of
registration data at a motel I run. I'm required to submit occupancy data to
the county on a monthly and annual basis. One of the requirements is that I
submit average room rates for each month. I've always used the AVERAGE
function to accomplish this but I'm concerned. I've read that the average
function is used for calculating for a contiguous row or column with a max
of 30 entries. My spreadsheet may have upwards of 200 entries each month.
Does this mean the AVERAGE function is unusable in this application? If this
be the case how would I accomplish this without cumbersome calculations?
Thanks in advance for any comments.
Dusty

Reply to this Message

Peo Sjoblom - 21 Sep 2007 19:13 GMT

It works fine, you just need some extra parenthesis, as an example

=AVERAGE((B4,B6,B7,B9,B10,B12,B13,B15,B16,B18,B19,B21,B22,B25,B28,B30,B33,B36,B39,B42,B45,B48,B52,B55,B55,B58,B60,B42,B48,B52,B57,B60,B48,B55,B59,B60))

works fine, just add an extra parenthesis for each 30 mark so if you have
over 60 entries use 3 parenthesis and so on

Signature

Regards,

Peo Sjoblom

> Howdy Techie Folks,
> I use Excel for a variety of functions, one of which is keeping track of
[quoted text clipped - 9 lines]
> Thanks in advance for any comments.
> Dusty

Reply to this Message

Dusty - 21 Sep 2007 19:49 GMT

Look like this is all a moot point anyway. I went back and looked at several
months data and the average finction returns the same value as totaling the
column and dividing it by the count of entries. So now my question becomes
where the 30 count limit comes from? Sorry to bother you all with useless
requests. I'd just like to know the implications of my ignorance.
Dusty

> Howdy Techie Folks,
> I use Excel for a variety of functions, one of which is keeping track of
[quoted text clipped - 9 lines]
> Thanks in advance for any comments.
> Dusty

Reply to this Message

Bernie Deitrick - 21 Sep 2007 20:02 GMT

Dusty,

The limit is for arguments - cells or ranged entered as separate entries:

=AVERAGE(A1,A2,A3,A4,A5:A10)

are five separate entries.

=AVERAGE(A1:A10)

will average those 10 cells - but the A1:A10 counts as just one entry.

HTH,
Bernie
MS Excel MVP

> Look like this is all a moot point anyway. I went back and looked at several months data and the
> average finction returns the same value as totaling the column and dividing it by the count of
[quoted text clipped - 12 lines]
>> Thanks in advance for any comments.
>> Dusty

Reply to this Message