+ Reply to Thread
Results 1 to 7 of 7

Calculating rolling average ignoring 0 values

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    3

    Calculating rolling average ignoring 0 values

    Hi, My first post here.

    I have spent all day trying to figure out how to solve this problem I have and google wasn't too much help on this so I decided to ask here. I apologise if this is overly simple but I just can't figure it out and I am not that experienced with excel.

    What I have is a spreadsheet with a whole bunch of sporting data on it. see attachment for an idea of the layout, Stats.jpg

    In these data each round a player is given a rating. What I want to do is calculate is the average rating for each player across the last 3 and 5 games they have played. When a player misses a game they get a rating of 0 for the round. I want 0 ratings to be ignored in the running averages. Additionally, games yet to be played have a 0 in the cell and should be ignored. To get overall averages ignoring 0's was simple I used the averageif function, but I couldn't figure out how to use that but only extract the last 3 non 0 values. I currently have data up to round 12, so if a player has played in 12, 11 and 10 then the 3 round average should just be the average of these 3, but if they missed for example round 11 then the 3 round average should return the average of 9, 10 and 12.

    The data is set out in the following way.
    In column B I have a list of player names from cells B3:B618.
    Across row 1 from N1:AM1 are labels "round 1" "round 2" etc.
    the ratings for each player in each round are in N-AM and from 3-618. i.e. N3:AM3 contains the weekly rating for player 1, 4 contains player 2 etc.
    I would like the 3 round averages to be returned for each player in column L and 5 round averages in column M.

    From looking around google it looked like average(offset... might be the solution but I don't understand that function and I was also unsure I could use it whilst ignoring 0 cells.

    Thank you in advance
    Whitz

  2. #2
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: Calculating rolling average ignoring 0 values

    Hi whitz,

    Please provide sample excel.
    Click on "* Add Reputation" as a way to say thanks

  3. #3
    Registered User
    Join Date
    06-06-2014
    Posts
    3

    Re: Calculating rolling average ignoring 0 values

    Thanks for your reply Pareshj


    Attached is a simplified spreadsheet with mostly just the relevant information for 8 players.

    The data that for averaging is in columns N:AM and I would like the outputs in L and M

    I appreciate any advice on this

    Test Data.xlsx

  4. #4
    Registered User
    Join Date
    06-06-2014
    Posts
    5

    Re: Calculating rolling average ignoring 0 values

    Hi Whitz,

    One way to achieve the purpose is to adopt the array formula.

    For example, you may try to input the following formula in cell "L3" for calculating the average ranking score (skip zero score) in last 3 round for player 1:
    =SUM(OFFSET(A3,,LARGE((N3:AM3>0)*COLUMN(N3:AM3),3)-1,1,LARGE((N3:AM3>0)*COLUMN(N3:AM3),1)-LARGE((N3:AM3>0)*COLUMN(N3:AM3),3)+1))/3

    Please be reminded that you may also need to press "Ctrl + Shift + Enter" at the same time when adopting "Array Formula".

    Hope this can help!

    Thanks,
    BNLY

  5. #5
    Registered User
    Join Date
    06-06-2014
    Posts
    3

    Re: Calculating rolling average ignoring 0 values

    BNLY thank you so much.

    I really don't understand exactly what this function is doing but it is definitely working.

    I am going to have to spend some time trying to figure it out so I can do similar things later.

    I appreciate your help

    Whitz

  6. #6
    Registered User
    Join Date
    06-06-2014
    Posts
    5

    Re: Calculating rolling average ignoring 0 values

    Hi Whitz,

    I can explain to you about the concept of the function step by step:
    1. (N3:AM3>0)*COLUMN(N3:AM3) means selecting a set of column positions associated with non-zero rankings for player1.
    2. LARGE((N3:AM3>0)*COLUMN(N3:AM3),3) means locating the column position associated with the non-zero ranking in the latest 3rd round.
    3. LARGE((N3:AM3>0)*COLUMN(N3:AM3),1) means locating the column position associated with the non-zero ranking in the last round.
    4. "Offset" formula applied here is to lock the relevant range between column position in step 2 and the column position in step 3.
    5. Once you get the targeted area, which is relevant to account for rolling average, it can be summated directly and then take average.

    Should you have any more question, just feel free to discuss.

    Thanks,
    BNLY

  7. #7
    Forum Contributor
    Join Date
    01-28-2013
    Location
    Rugby
    MS-Off Ver
    365
    Posts
    205

    Re: Calculating rolling average ignoring 0 values

    Apologies for hijacking this post, I'm looking for exactly the same thing, and that is ignore the 0 in cells that generate from other formulas, currently using this
    =AVERAGE(OFFSET('Daily Averages'!D5,0,COUNT('Daily Averages'!D5:BE5)-P13,1,P13)) any ideas would be appreciated

    Daily Averages'!D5:BE5 - data range
    P13 - where i select the qty of average days

+ 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. Calculating rolling average horozontally
    By Mr Rodger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2014, 03:48 PM
  2. [SOLVED] Rolling average with data in a row using only last 10 entries ignoring blanks
    By Lasers Reef in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2013, 09:37 PM
  3. Replies: 2
    Last Post: 03-31-2011, 09:18 AM
  4. Calculating a rolling average
    By roasthawg in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-16-2009, 02:45 PM
  5. [SOLVED] Formula for calculating a rolling 12 month average in excel?
    By Jeff in forum Excel General
    Replies: 1
    Last Post: 12-09-2005, 05:15 PM

Tags for this Thread

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