dynamic 'average if' formula--what am I doing wrong?

[Terry B.]

I've got a table with roughly 10 columns, numeric data in all but
header. Col E has entries in every cell; col I has some cell values of
8.00, some of 16.00, some of 24.00--and many of col I's cells are
blank. WHAT I'M TRYING TO DO is to calculate an average of col E
entries--BUT ONLY those cell-entries that correspond to col I records
of "8.00". (Or, 16 or 24, it doesn't matter...) AND I NEED THIS TO BE
A DYNAMIC RANGE FORMULA, so that it's going to work & continue to work
accurately as far down the column as I enter data!
This is what I've been using (and what doesn't work):

=AVERAGE(IF(OFFSET(I2,0,0,COUNTA(I:I),1)=8.00,OFFSET(E2,0,0,COUNTA(E:E),1)))

I've been entering this as array, and nothing happens after I do,
the cell I entered it in just remains blank (!?) I've also tried
putting quotes around the `=8.00` , or quotes just around the ` 8.00`.
No joy. :-(

I sure would appreciate any help I can get with this
situation....Thanks!

Terry B.

 

[Biff]

Hi Terry,

Here's one way based on the info you provided:

=AVERAGE(IF(I1:I65535<>"",E1:E65535))

If this formula is not located at the bottom of the range
there is no reason to use the offset to find a dynamic
range. You can just include the whole column minus one
cell as the range. Also, if column I will only contain
values of 8,16, or 24, you could simplify the criteria to
<is not mt>, <>. This sure makes the formula alot simpler.

Is this too simplistic of a solution?

Biff

 

[Biff]

That is an array formula.

Biff

-----Original Message-----
Hi Terry,

Here's one way based on the info you provided:

=AVERAGE(IF(I1:I65535<>"",E1:E65535))

If this formula is not located at the bottom of the range
there is no reason to use the offset to find a dynamic
range. You can just include the whole column minus one
cell as the range. Also, if column I will only contain
values of 8,16, or 24, you could simplify the criteria to
<is not mt>, <>. This sure makes the formula alot simpler.

Is this too simplistic of a solution?

Biff
in
all but around
the ` 8.00`.
.

 

[Aladin Akyurek]

I've got a table with roughly 10 columns, numeric data in all but
header. Col E has entries in every cell; col I has some cell values of
8.00, some of 16.00, some of 24.00--and many of col I's cells are
blank. WHAT I'M TRYING TO DO is to calculate an average of col E
entries--BUT ONLY those cell-entries that correspond to col I records
of "8.00". (Or, 16 or 24, it doesn't matter...) AND I NEED THIS TO BE
A DYNAMIC RANGE FORMULA, so that it's going to work & continue to work
accurately as far down the column as I enter data!
This is what I've been using (and what doesn't work):

=AVERAGE(IF(OFFSET(I2,0,0,COUNTA(I:I),1)=8.00,OFFSET(E2,0,0,COUNTA(E:E),1)))

I've been entering this as array, and nothing happens after I do,
the cell I entered it in just remains blank (!?) I've also tried
putting quotes around the `=8.00` , or quotes just around the ` 8.00`.
No joy. :-(

I sure would appreciate any help I can get with this
situation....Thanks!

Terry B.

 

[Ken Wright]

The one thing I would do with your formula is to change it so that both parts of
the formula rely on the SAME criteria for the range length. Ranges must be the
same length for an array to work, and it won't affect your data one jot to
change it as i have described, eg:-

=AVERAGE(IF(OFFSET(I2,0,0,COUNTA(I:I),1)=8.00,OFFSET(E2,0,0,COUNTA(I:I),1)))

In the formula you had, it is entirely possible that COUNTA(E:E) was returning a
different value to that of COUNTA(I:I) and this would ensure that the formula
bombed. You are using those for nothing more than to define the range length,
so just pick one of them, any of them.

Also, you obviously need to make sure you array enter it using CTRL+SHIFT+ENTER,
so that if entered correctly, you will actually see the following in the cell:-

{=AVERAGE(IF(OFFSET(I2,0,0,COUNTA(I:I),1)=8.00,OFFSET(E2,0,0,COUNTA(I:I),1)))}

Note: These brackets CANNOT be entered manually, you must array enter it as
described.

Lastly, be very sure that COUNTA gives you what you want for this, as if you
have any blanks in the data, then the ranges Excel is calculating may be shorter
than you think. A space in the title row or any gaps anywhere in the column you
are using for your COUNTA() will decrement the COUNTA from what I suspect you
are looking for as in the total number of rows to the last piece of data, and
mean it is not equal to the last row number.

 

[Aladin Akyurek]

=AVERAGE(IF(I2:INDEX(E:E,MATCH(9.99999999999999E+307,E:E))="08:00"+0,E2:INDE
X(E:E,MATCH(9.99999999999999E+307,E:E))))

which must be confirmed with control+shift+enter instead of just with enter.

If you use a cell (say H1) for...

=MATCH(9.99999999999999E+307,E:E)

you can have a shorter formula:

=AVERAGE(IF(I2:INDEX(E:E,H1)="08:00"+0,E2:INDEX(E:E,H1)))