+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    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
    OnditiGK

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,328

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

    Hi

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

    The other formulas are similar.

    See the file
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    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.

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,328

    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?

  5. #5
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    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.

  6. #6
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    289

    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!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Forecasting Wins, Draws, Loss
    By fab4x in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2016, 02:54 PM
  2. Formula to increase number based on WINS/LOSSES or FINAL SCORE
    By tivory in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2016, 07:31 PM
  3. Counting Wins and Losses
    By chester1993 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-28-2016, 09:52 PM
  4. Formula to calculate half wins and half losses
    By chilli76 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-27-2014, 07:33 AM
  5. Lookup and Calculate Wins and Losses from Multiple Sheets?
    By Tomkat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2012, 03:47 PM
  6. Streak on Wins and Losses
    By palerider85 in forum Excel General
    Replies: 6
    Last Post: 10-24-2011, 01:05 AM
  7. Track wins, loses, win average and Standings
    By coondawg68 in forum Excel General
    Replies: 0
    Last Post: 07-07-2011, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1