Small Array is too big for AVERAGE Function?
 

Hi. I have an array where if a certain value (in cell T28) matches an entry
in column A of array A3:D82, it returns the corresponding value in column D.
The formula is:
{=AVERAGE(IF(T28=A3:A82,D3:D82))}.

This formula has worked great up until today. But today T28 corresponded
with a value more than half way down the array, and the array formula returns
Div/0. I know that the equation is correct because if I change one of the
higher rows to the same number, it again works correctly.

This surprises me since my table is not that large. Anyone ever run into
this problem and/or a solution!

Thanks!

Small Array is too big for AVERAGE Function?
 

I don't get a div/0 error.

I used your formula and have the following values in the following cells:

T28: 0
D12: 4
A11: 2

I do, however, get a div/0 error if column D doesn't contain any data.

Dave
--
Brevity is the soul of wit.


"daven123" wrote:

Hi. I have an array where if a certain value (in cell T28) matches an entry
in column A of array A3:D82, it returns the corresponding value in column D.
The formula is:
{=AVERAGE(IF(T28=A3:A82,D3:D82))}.

This formula has worked great up until today. But today T28 corresponded
with a value more than half way down the array, and the array formula returns
Div/0. I know that the equation is correct because if I change one of the
higher rows to the same number, it again works correctly.

This surprises me since my table is not that large. Anyone ever run into
this problem and/or a solution!

Thanks!

Small Array is too big for AVERAGE Function?
 

Another possibility is the value corresponding to the one in T28 (in
column D) was accidentally made a Text value at some point.

Scott

Dave F wrote:
I don't get a div/0 error.

I used your formula and have the following values in the following cells:

T28: 0
D12: 4
A11: 2

I do, however, get a div/0 error if column D doesn't contain any data.

Dave
--
Brevity is the soul of wit.


"daven123" wrote:

Hi. I have an array where if a certain value (in cell T28) matches an entry
in column A of array A3:D82, it returns the corresponding value in column D.
The formula is:
{=AVERAGE(IF(T28=A3:A82,D3:D82))}.

This formula has worked great up until today. But today T28 corresponded
with a value more than half way down the array, and the array formula returns
Div/0. I know that the equation is correct because if I change one of the
higher rows to the same number, it again works correctly.

This surprises me since my table is not that large. Anyone ever run into
this problem and/or a solution!

Thanks!

Small Array is too big for AVERAGE Function?
 

Scott wrote...
Another possibility is the value corresponding to the one in T28 (in
column D) was accidentally made a Text value at some point.
....

Yet another possibility is that if T28 and A3:A82 were all numeric,
they may appear equal but actually contain slightly different values.
That is, 2.3 and 2.30000000001 would likely appear the same on screen
as cell values, but they won't be equal in the array formula. First
thing to check is whether the formula =COUNTIF(A3:A82,T28) returns a
positive number.

Small Array is too big for AVERAGE Function?
 

i agree Harlan specially when either T28 or A3:A82 are triggered by formula.
A rounding formula may be necessary...on either T28 or A3:A82...wherever..

"Harlan Grove" wrote:

Scott wrote...
Another possibility is the value corresponding to the one in T28 (in
column D) was accidentally made a Text value at some point.
....

Yet another possibility is that if T28 and A3:A82 were all numeric,
they may appear equal but actually contain slightly different values.
That is, 2.3 and 2.30000000001 would likely appear the same on screen
as cell values, but they won't be equal in the array formula. First
thing to check is whether the formula =COUNTIF(A3:A82,T28) returns a
positive number.

Small Array is too big for AVERAGE Function?
 

Thanks to each of you for your suggestions. As it turns out, the only way I
could eliminate the problem was to change A3:A82 from incremented values
(A4=A3+0.1 etc.) to typed values (type 27.6, 27.7, 27.8,...). Weird how it
worked up to value 31.0, but not 31.1. I had played with designating field
types, etc., but nothing seemed to help. Seems to me like it could be a
hidden quirk/problem in Excel. An easy way to change it was to copy A3:A82
into Word, then copy it back into Excel, since copying into Word didn't
retain the formula.

Thanks again for all your help!
Dave

"driller" wrote:

i agree Harlan specially when either T28 or A3:A82 are triggered by formula.
A rounding formula may be necessary...on either T28 or A3:A82...wherever..

"Harlan Grove" wrote:

Scott wrote...
Another possibility is the value corresponding to the one in T28 (in
column D) was accidentally made a Text value at some point.
....

Yet another possibility is that if T28 and A3:A82 were all numeric,
they may appear equal but actually contain slightly different values.
That is, 2.3 and 2.30000000001 would likely appear the same on screen
as cell values, but they won't be equal in the array formula. First
thing to check is whether the formula =COUNTIF(A3:A82,T28) returns a
positive number.

Small Array is too big for AVERAGE Function?
 

Found another way to fix it - kept increment formula, but added ROUND to it.
Incrementing probably eventually added an invisible/small decimal to the
number - still seems like a quirk in Excel. Thanks again for your help.

"daven123" wrote:

Thanks to each of you for your suggestions. As it turns out, the only way I
could eliminate the problem was to change A3:A82 from incremented values
(A4=A3+0.1 etc.) to typed values (type 27.6, 27.7, 27.8,...). Weird how it
worked up to value 31.0, but not 31.1. I had played with designating field
types, etc., but nothing seemed to help. Seems to me like it could be a
hidden quirk/problem in Excel. An easy way to change it was to copy A3:A82
into Word, then copy it back into Excel, since copying into Word didn't
retain the formula.

Thanks again for all your help!
Dave

"driller" wrote:

i agree Harlan specially when either T28 or A3:A82 are triggered by formula.
A rounding formula may be necessary...on either T28 or A3:A82...wherever..

"Harlan Grove" wrote:

Scott wrote...
Another possibility is the value corresponding to the one in T28 (in
column D) was accidentally made a Text value at some point.
....

Yet another possibility is that if T28 and A3:A82 were all numeric,
they may appear equal but actually contain slightly different values.
That is, 2.3 and 2.30000000001 would likely appear the same on screen
as cell values, but they won't be equal in the array formula. First
thing to check is whether the formula =COUNTIF(A3:A82,T28) returns a
positive number.