# Adjust average formula to calculate average when a team wins, draws or losses

1. ## Adjust average formula to calculate average when a team wins, draws or losses

In the file attached, I need to be able to calculate the average goals when a team wins, draws or losses. The formulas in columns AB, AC and AD are correct. I need help adjusting these formulas so that they filter averages based on whether a team wins, draws or losses by looking at columns J and K.

Sample File.xlsx  Register To Reply

2. ## Re: Adjust average formula to calculate average when a team wins, draws or losses

Hi

Try this in AE6 and copy down
Formula:  `Please Login or Register  to view this content.`

The other formulas are similar.

See the file  Register To Reply

3. ## Re: Adjust average formula to calculate average when a team wins, draws or losses

One of the errors I realized is that the average wining goal for Arsenal in the last six matches (on home ground) is give as 3.600 when it should be 3.833.  Register To Reply

4. ## Re: Adjust average formula to calculate average when a team wins, draws or losses

Hi @Onditi

When you refer last six matches on home ground, Arsenal team scores 0-3, with Manchester City, 3-0 (Watford), 3-0 (Stoke), 3-2 (Southampton), 4-1 (West Ham) and 5-1 (Burnley). So for 5 Wins we get (3+3+3+4+5)/5=18/5=3.6.
How get you 3.833?  Register To Reply

5. ## Re: Adjust average formula to calculate average when a team wins, draws or losses

Sorry I did not clearly explain. Under Average Winning Goal, we look at matches where the teams won ONLY. Under Average Drawing Goal, We look at matches that ended in a Draw ONLY. And under Average losing goal, we look at matches where the teams lost ONLY.  Register To Reply

6. ## Re: Adjust average formula to calculate average when a team wins, draws or losses

Hi, Onditi!

Check the calculations in column AE and AF...
[AE6] : =IF(\$O6="","",SUMPRODUCT(--(COUNTIFS(OFFSET(J\$6,,,ROW(J\$6:J\$385)-ROW(J\$5)),"W",OFFSET(D\$6,,,ROW(J\$6:J\$385)-ROW(J\$5)),\$O6)>IF(Upcoming!\$B\$4="All Matches",,COUNTIFS(D\$6:D\$385,\$O6,J\$6:J\$385,"W")-Upcoming!\$B\$3)),--(D\$6:D\$385=\$O6),--(J\$6:J\$385="W"),F\$6:F\$385)/MIN(Upcoming!\$B\$3+1000*(Upcoming!\$B\$4="All Matches"),COUNTIFS(D\$6:D\$385,\$O6,J\$6:J\$385,"W")))

Check file. Blessings!  Register To Reply