Average formula limitation
 

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).

Average formula limitation
 

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen" wrote:

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).

Average formula limitation
 

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen" wrote:

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).

Average formula limitation
 

Are you values in contiguous cells?

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



"Karen" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen" wrote:

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).

Average formula limitation
 

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.

Average formula limitation
 

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?

"Duke Carey" wrote:

Are you values in contiguous cells?

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



"Karen" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen" wrote:

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).

Average formula limitation
 

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

--
Biff
Microsoft Excel MVP


"Spiky" wrote in message
...
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.

Average formula limitation
 

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.


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
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?

"Duke Carey" wrote:

Are you values in contiguous cells?

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



"Karen" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Karen" wrote:

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).

Average formula limitation
 

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" wrote:

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.

Average formula limitation
 

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.