"average" IF

[mpanty]

Hi.

I have a table with rating values for each game my team plays, and the
games played belong to different categories (league, friendly, cup
etc.).

My question is: is there a way to tell Excel to calculate the average
of only league games, friendly games, and cup games separately?

For example:

*Game * -1 / 2 / 3 / 4 / 5 / 6-
*Category * -L / L / F / C / L / C-
*Rating *- 6.7 / 5.6 / 5.3 / 7.5 / 6.7 / 7.8-

Is there a way to tell Excel: pick only the values which have
corresponding column values of L (for league) and calculate an average
of only those values? Then for friendly (F) and Cup (C) games?

Thanks for your help.

 

[Dave O]

Look at SUMIF and COUNTIF functions, then set up your average
calculation by dividing the result of the SUMIF by the COUNTIF.

 

[Domenic]

Assuming that A1:G3 contains your data, including your row header,
try...

=AVERAGE(IF(B2:G2="L",B3:G3))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If you enter L,
F, and C in cells A10:A12, you could enter the following formula in B10
and copy down:

=AVERAGE(IF($B$2:$G$2=A10,$B$3:$G$3))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[mpanty]

Hi Dave and Dominic,

Thanks for your help, both of you. I tried both suggestions and got th
same result, which is good news because Dominic's formula is simpler t
set up, so it confirms I've managed to make it work.

The only problem in both formulas is that they count blank cells as
"zero".

For example (because I have several players to which the formul
applies to, and some play more league games than others, while other
play friendlies):

*Game*-...... 001 / 002 / 003 / 004 / 005 / 006-
*Category *-. 'L' / 'L' / 'F' / 'C' / 'L' / 'C'-
*James *-.... 6.7 / 5.6 / --- / 7.5 / --- / 7.8-
*Chris *-.... --- / --- / 6.7 / 5.6 / --- / ----

So for *James* for example, the formula you suggested Dominic, woul
use 6.7, 5.6 and 0 to calculate the LEAGUE (L) average, yielding 4.1
instead of just the first two league games the player actually played
and ignoring the 3rd (giving the correct average value 6.2).

Is there a way to use the formula so that it ignores blank cells (o
doesn't count them in the average)

 

[Domenic]

Try the following formula instead...

=AVERAGE(IF((B2:G2="L")*(B3:G3<>""),B3:G3))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[mpanty]

Perfect Domenic! Worked like a charm!

Thank you so much for your help!