If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"average" IF
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. -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=399295 |
#2
|
|||
|
|||
Look at SUMIF and COUNTIF functions, then set up your average
calculation by dividing the result of the SUMIF by the COUNTIF. |
#3
|
|||
|
|||
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 Wrote: 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. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=399295 |
#4
|
|||
|
|||
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)? -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=399295 |
#5
|
|||
|
|||
Try the following formula instead... =AVERAGE(IF((B2:G2="L")*(B3:G3""),B3:G3)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! mpanty Wrote: 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)? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=399295 |
#6
|
|||
|
|||
Perfect Domenic! Worked like a charm! Thank you so much for your help! -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=399295 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What is this kind of average called? | havocdragon | Worksheet Functions | 3 | June 24th, 2005 05:10 PM |
Trying to Average an Average | Joe | Running & Setting Up Queries | 5 | April 4th, 2005 07:05 PM |
Arrary formula for Average includes blank cells | jan | Worksheet Functions | 4 | May 14th, 2004 11:19 PM |
Using array-entered formulas for calculation average | Kieran | Worksheet Functions | 4 | March 1st, 2004 04:00 AM |
Problems using Average | MeritageSue | Worksheet Functions | 3 | February 1st, 2004 02:48 AM |