Average formula limitation

[Karen]

Does anyone know if there is a limit to the number of cells you can use in an
average formula?
I'm trying to build an average for weekly number of phone calls for each
week so there should be about 52 cells to get an average from(one for each
week, save the holiday weeks).

 

[John Bundy]

I just averaged 50k of them so that is not the issue. I would make sure none
are stored as text. next step is to average the first 2, then 3, 4 etc til
you find the problem cell. More than likely a format cells will resolve your
issue.

 

[Karen]

Ok, I'll try that John, thanks. It very well could be a formula issue.

 

[Duke Carey]

Are you values in contiguous cells?

Are the values numeric, i.e., are some of them text values that LOOK like
numbers?

 

[Spiky]

John probably averaged them with a single range, like
AVERAGE(A1:A50000). If you are trying to enter each cell in the
formula individually, like AVERAGE(A1,A4,A6,A10), then the limit is 30
in a formula. If that is the case, try to come up with a different way
to do this, perhaps using ranges. There can be blank cells or text in
the range that won't affect your calculation, so you can run a range
across some things, just not other numbers.

 

[Karen]

They are actually every 6th cell and have a formula in them adding the five
day total of phone calls for those five days so there is a formula in this
particular cell. It tells me I have chosen too many arguments for this
function. So, it looks like I can average an unlimited number of simple cells
(just a number, no formulas) but if there is a formula inside it, it will be
limited to 30 of those cells. Is this the case that you know of?

 

[T. Valko]

If you are trying to enter each cell in the formula

individually, like AVERAGE(A1,A4,A6,A10),
then the limit is 30 in a formula.

The number of *arguments* is limited to 30**. You can get around that by
using multiple area references:

=AVERAGE((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),(B3:B5,B9:B11),C1:C20)

Each subset of (...) is one argument.

The above formula has 3 arguments. So, you still have up to 27 more
arguments that can be used.

**
Excel versions prior to Excel 2007 = 30 arguments
Excel 2007 = 255 arguments

 

[T. Valko]

Is this the case that you know of?

No. See my reply in another branch of this thread.

They are actually every 6th cell

Tell us the first few cells that need to be averaged. We'll be able to see
the pattern and figure it out. For example, average cells A2, A8, A14, A20.

 

[Karen]

Yep, you're right, the limit was 30. I may need you to elaborate on what you
mean by using a range to calculate this average. This is not an immediate
need so by all means take your time but I'm unaware of the alternatives to
creating the average from these 52 cells that have formulas in them. So, I'm
averaging a sum.
I really do appreciate any suggestions you or anyone may have.

 

[Spiky]

An argument is everything between commas inside parentheses. Like:
(A,B,C,D) <<--- There are 4 arguments in there.

Look at post #8 by Biff for ideas on how to create yours. You have to
pay close attention to where the parentheses are to understand the
"argument" syntax.