Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average with criteria for adjacent cells ?
I'm a bit stuck on a situation. I could greatly use some help. I have been keeping track of NBA scores in excel. Let's just use the chicago bulls in this example. I am trying to find the average amount of points that the bulls win by, on only "won" games (not games they've lost)
I have 4 columns labeled as follows: | W/L | Pts | Opponent Pts | Difference | Each row will contain data from 1 game. 10 rows would have scores for 10 games. When the amount of points the bulls score are greater than their opponent's score, a "W" will appear in the first column. And when they score less, a "L" will appear instead. (I've written the function for this already) The points scored will be calculated, and difference is shown in the fourth column. (I've written the function for this already) Now here's what I'd like to do... I'd like a formula that will give me the average of all the data in the fourth column, with the criteria that only if the first column contains a "W" Could someone explain the function i would need to write in order to achieve this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with criteria for adjacent cells ?
John Yaris wrote on 2/14/2012 :
I'm a bit stuck on a situation. I could greatly use some help. I have been keeping track of NBA scores in excel. Let's just use the chicago bulls in this example. I am trying to find the average amount of points that the bulls win by, on only "won" games (not games they've lost) I have 4 columns labeled as follows: | W/L | Pts | Opponent Pts | Difference | Each row will contain data from 1 game. 10 rows would have scores for 10 games. When the amount of points the bulls score are greater than their opponent's score, a "W" will appear in the first column. And when they score less, a "L" will appear instead. (I've written the function for this already) The points scored will be calculated, and difference is shown in the fourth column. (I've written the function for this already) Now here's what I'd like to do... I'd like a formula that will give me the average of all the data in the fourth column, with the criteria that only if the first column contains a "W" Could someone explain the function i would need to write in order to achieve this? Just off the top of my head... <speudo formula) =sumif(col_1,"W",col_4)/countif(col_1,"W") ...but don't know how your sheet is laid out. If each team has its own sheet then this formula should work. If there are multiple teams on a sheet then you'll have to assign defined names for each team section and use those names in your formulas. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Non-Adjacent Cells in Budget Spreadsheet | Excel Worksheet Functions | |||
Average Non-Adjacent Cells in Budget Spreadsheet | Excel Worksheet Functions | |||
Another Average non-adjacent cells question... | Excel Discussion (Misc queries) | |||
How do I exclude zero's from an average of five non-adjacent cells | Excel Programming | |||
Average non-adjacent cells if the cell does not contain zero | Excel Discussion (Misc queries) |