why can't I average 36 values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to average 36 seperate values in 36 different cells but I keep
getting an error. Is there a limit on how many cells can be used in a
function? If not, what am I doing wrong? If there is a limit, how do I get
around this issue?
 
What formula are you using?

I'm not aware of such a limit, however, if there were such a limit, the
solution would be to split the range of cells to be averaged into to ranges,
average each of those ranges, and then average the two averages.

Dave
 
hi Dave,

You should answer back in the Questioner's forum. Your answers are
always good, but I need the questions too.

Hope you will help me too to understand your posts.


Thanks,

Shail
 
I am trying to average 36 seperate values in 36 different cells but I keep
getting an error. Is there a limit on how many cells can be used in a
function? If not, what am I doing wrong? If there is a limit, how do I get
around this issue?
Click to expand...

From Excel Specifications:

Arguments in a function 30

You "get around" it by referencing the cells with less than 30 arguments.

For example: =AVERAGE(A1:A36)

If the cells are non-contiguous, you may be able to group them or NAME the
non-contiguous range, or use some kind of formula to refer to the
non-contiguous range.

Worst case scenario where neither of the above is applicable:

=(A1+B4+C16+D92+.....) / 36


--ron
 
Note that the average of the averages of unequal ranges may not be the same
as the average of the whole range.
e.g avgerage of 100 = 100, average of 1,1,1 = 1
average of averages is about 50, average of the four values is about 25.
 
hi Dave,

Sorry to confuse you, but I just wanted if you can answer it back from
where someone has asked you the question. So that anyone can read the
question and your answer too.


Thanks again Dave,

Shail
 
Well, you have me really confused now, because I can just scroll down and see
the original question!

Sorry, not sure why you can't see the original question???

Dave
 
Right, but when the set of items is an even number, as in this case (36),
AVERAGE(A+B+C+D) = AVERAGE(AVERAGE(A+B),AVERAGE(C+D))

In the case of an uneven number of values, then the solution would be to sum
up the range and divide by the count; i.e., =SUM(A1:A1001)/COUNT(A1:A1001).

Dave
 
Is there some kind of pattern, like every other cell? Every 5th cell?

You can always use multiple range references:

=AVERAGE((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,
A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,
A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36))

There can be up to 30 arguments but using multiple range references the
above formula contains only 1 argument. Of course, you wouldn't need this
for the sample range used because it's a contiguous range. This is only an
example.

Biff
 
Is there some kind of pattern, like every other cell? Every 5th cell?

You can always use multiple range references:

=AVERAGE((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,
A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,
A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36))

There can be up to 30 arguments but using multiple range references the
above formula contains only 1 argument. Of course, you wouldn't need this
for the sample range used because it's a contiguous range. This is only an
example.

Biff
 
shail wrote...
Sorry to confuse you, but I just wanted if you can answer it back from
where someone has asked you the question. So that anyone can read the
question and your answer too.
Click to expand...
....

You're using Google Groups, but the OP posted through Microsoft's
newsgroup web portal. Microsoft being Microsoft, few if any original
postings through their web portal are ever pushed out to other NNTP
(news) servers. However, Microsoft being Microsoft, responses are
pushed out to other NNTP servers.

This is just something you need to learn to live with when using Google
Groups to read Microsoft newsgroups. Dave F was doing the right thing,
and *was* responding in the same newsgroup as the OP.
 
shail wrote...
Sorry to confuse you, but I just wanted if you can answer it back from
where someone has asked you the question. So that anyone can read the
question and your answer too.
Click to expand...
....

You're using Google Groups, but the OP posted through Microsoft's
newsgroup web portal. Microsoft being Microsoft, few if any original
postings through their web portal are ever pushed out to other NNTP
(news) servers. However, Microsoft being Microsoft, responses are
pushed out to other NNTP servers.

This is just something you need to learn to live with when using Google
Groups to read Microsoft newsgroups. Dave F was doing the right thing,
and *was* responding in the same newsgroup as the OP.
 
Thanks Harlan,

This might be too technical to understand, but I got Dave and the
original question.


Thanks again,

Shail
 
Thanks Harlan,

This might be too technical to understand, but I got Dave and the
original question.


Thanks again,

Shail
 
Back
Top