"average" IF

Hi Dave and Dominic,

Thanks for your help, both of you. I tried both suggestions and got the same result, which is good news because Dominic's formula is simpler to 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 a "zero".

For example (because I have several players to which the formula applies to, and some play more league games than others, while others 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, would 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 (or doesn't count them in the average)?

Try the following formula instead...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by mpanty

Hi Dave and Dominic,

Thanks for your help, both of you. I tried both suggestions and got the same result, which is good news because Dominic's formula is simpler to 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 a "zero".

For example (because I have several players to which the formula applies to, and some play more league games than others, while others 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, would 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 (or doesn't count them in the average)?

Perfect Domenic! Worked like a charm!

Thank you so much for your help!