Averageif puzzle

[GB3 via OfficeKB.com]

I'm attempting to average values depending upon a category name (using Excel
2007).
I thought this formula would work to average values for Group 1 over the
array B2:E7 but
I'm arriving at an incorrect result - as compared to my manually
calculated average (Avg) below:

The formula I used to attempt the average for Group 1 over the array was:
=AVERAGEIF(A2:A7,"Group 1",B2:E7)

Can someone advise me about what I'm doing wrong? Thank you.

A B C D E Avg Averageif
Group 1 2 1 0 1 1.09 1.33
Group 2 0 0 1 0 0.55 1.00
Group 1 1 1 0 0
Group 2 3 0 1 1
Group 1 1 3 2
Group 2 0 0 0

 

[T. Valko]

You're trying to compare a 1 dimensional array, A2:A7, to a 2 dimensional
array B2:E7. So, you're formula:

=AVERAGEIF(A2:A7,"Group 1",B2:E7)

Was working as though it was written like this:

=AVERAGEIF(A2:A7,"Group 1",B2:B7)

Try this array formula** :

=AVERAGE(IF(A2:A7="Group 1",IF(ISNUMBER(B2:E7),B2:E7)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[GB3 via OfficeKB.com]

Thanks, T. That worked. I appreciate the explanation too.
How about this little complication.
I want to average the summed scores in a game (i.e., across periods) - not
the per period average. So, here Groups 1 and 2 play several games (best of
5) with optional OT period

Q1 Q2 Q3 Q4 OT TOT Per game avg
Group 1 0 1 1 0 2 Group 1 4.3
Group 2 2 0 0 0 2 Group 2 2.0
Group 1 2 2 0 2 6
Group 2 0 0 2 0 2
Group 1 3 0 0 2 5
Group 2 0 1 1 0 2
Group 1 0
Group 2 0
Group 1 0
Group 2 0


Now the problem is I have written the table to sum the scores for each
group's series of scores by period - and want to calculate the average score
by group by game (not by period). The added difficulty is that I don't want
"games" 4 and 5 entered into the mix since no scores were entered. However
the sum formula creates a "zero" for the TOT column, so I can't simply
average the TOTs by group (Excel wants to also enter the zeroes).
Furthermore, it's possible that a group might legitimately have a zero total
(see the second Group 2 entries) - so I can't use logic to just eliminate
zeroes in the TOT column from being considered.

Thanks again for any guidance you can provide.

You're trying to compare a 1 dimensional array, A2:A7, to a 2 dimensional
array B2:E7. So, you're formula:

=AVERAGEIF(A2:A7,"Group 1",B2:E7)

Was working as though it was written like this:

=AVERAGEIF(A2:A7,"Group 1",B2:B7)

Try this array formula** :

=AVERAGE(IF(A2:A7="Group 1",IF(ISNUMBER(B2:E7),B2:E7)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

I'm attempting to average values depending upon a category name (using
Excel

[quoted text clipped - 16 lines]

Group 1 1 3 2
Group 2 0 0 0

 

[T. Valko]

How about if you change your Total formula to something like this:

=IF(COUNT(B2:F2),SUM(B2:F2),"")

That way, instead of returning 0 the formula will return a blank until
scores are entered. Then, you can go back to using the AVERAGEIF function:

=AVERAGEIF(A$2:A$11,I2,G$2:G$11)

Where I2 = Group 1, G$2:G$11 = Total score

--
Biff
Microsoft Excel MVP

Thanks, T. That worked. I appreciate the explanation too.
How about this little complication.
I want to average the summed scores in a game (i.e., across periods) - not
the per period average. So, here Groups 1 and 2 play several games (best
of
5) with optional OT period

Q1 Q2 Q3 Q4 OT TOT Per game avg
Group 1 0 1 1 0 2 Group 1 4.3
Group 2 2 0 0 0 2 Group 2 2.0
Group 1 2 2 0 2 6
Group 2 0 0 2 0 2
Group 1 3 0 0 2 5
Group 2 0 1 1 0 2
Group 1 0
Group 2 0
Group 1 0
Group 2 0


Now the problem is I have written the table to sum the scores for each
group's series of scores by period - and want to calculate the average
score
by group by game (not by period). The added difficulty is that I don't
want
"games" 4 and 5 entered into the mix since no scores were entered.
However
the sum formula creates a "zero" for the TOT column, so I can't simply
average the TOTs by group (Excel wants to also enter the zeroes).
Furthermore, it's possible that a group might legitimately have a zero
total
(see the second Group 2 entries) - so I can't use logic to just eliminate
zeroes in the TOT column from being considered.

Thanks again for any guidance you can provide.

You're trying to compare a 1 dimensional array, A2:A7, to a 2 dimensional
array B2:E7. So, you're formula:

=AVERAGEIF(A2:A7,"Group 1",B2:E7)

Was working as though it was written like this:

=AVERAGEIF(A2:A7,"Group 1",B2:B7)

Try this array formula** :

=AVERAGE(IF(A2:A7="Group 1",IF(ISNUMBER(B2:E7),B2:E7)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

I'm attempting to average values depending upon a category name (using
Excel

[quoted text clipped - 16 lines]

Group 1 1 3 2
Group 2 0 0 0

 

[GB3 via OfficeKB.com]

Thanks again, T. Clever. So I read this as saying, if the cells specified
are numerals, add them; else return a blank? I would never have thought to
use a COUNT function in this context.

How about if you change your Total formula to something like this:

=IF(COUNT(B2:F2),SUM(B2:F2),"")

That way, instead of returning 0 the formula will return a blank until
scores are entered. Then, you can go back to using the AVERAGEIF function:

=AVERAGEIF(A$2:A$11,I2,G$2:G$11)

Where I2 = Group 1, G$2:G$11 = Total score

Thanks, T. That worked. I appreciate the explanation too.
How about this little complication.

[quoted text clipped - 54 lines]

 

[T. Valko]

So I read this as saying, if the cells specified

are numerals, add them; else return a blank?

Yes

--
Biff
Microsoft Excel MVP

Thanks again, T. Clever. So I read this as saying, if the cells
specified
are numerals, add them; else return a blank? I would never have thought
to
use a COUNT function in this context.

How about if you change your Total formula to something like this:

=IF(COUNT(B2:F2),SUM(B2:F2),"")

That way, instead of returning 0 the formula will return a blank until
scores are entered. Then, you can go back to using the AVERAGEIF function:

=AVERAGEIF(A$2:A$11,I2,G$2:G$11)

Where I2 = Group 1, G$2:G$11 = Total score

Thanks, T. That worked. I appreciate the explanation too.
How about this little complication.

[quoted text clipped - 54 lines]

Group 1 1 3 2
Group 2 0 0 0