Averageif puzzle
 

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

--
Message posted via http://www.officekb.com

Averageif puzzle
 

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.

--
Biff
Microsoft Excel MVP


"GB3 via OfficeKB.com" <u57077@uwe wrote in message
news:a1faa0351db22@uwe...
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

--
Message posted via http://www.officekb.com

Averageif puzzle
 

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.

T. Valko wrote:
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

--
Message posted via http://www.officekb.com

Averageif puzzle
 

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


"GB3 via OfficeKB.com" <u57077@uwe wrote in message
news:a1fb6728dda93@uwe...
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.

T. Valko wrote:
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

--
Message posted via http://www.officekb.com

Averageif puzzle
 

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.

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

Averageif puzzle
 

So I read this as saying, if the cells specified
are numerals, add them; else return a blank?

Yes

--
Biff
Microsoft Excel MVP


"GB3 via OfficeKB.com" <u57077@uwe wrote in message
news:a2066a2acc939@uwe...
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.

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1