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
OnditiGK
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
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.
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?
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.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks