# Calculating rolling average ignoring 0 values

1. ## 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.

Whitz

Hi whitz,

3. ## Re: Calculating rolling average ignoring 0 values

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. ## 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. ## 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.

Whitz

6. ## 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

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