Averaging last 5 values having issues with blank cells

[joeblow7]

I created a new score tracker for my golf league, but I'm having issue with it calculating the new averages after the 2nd week got rained out. The image below is where it will track each players round and take the average from. We use a 5 week rolling average. I have the average being calculated with :

=IF(COUNT('Players and Scores'!B3:AA3)>=5,AVERAGE(OFFSET('Players and Scores'!B3,0,COUNT('Players and Scores'!3:3)-5,1,5)), AVERAGE('Players and Scores'!B3:AA3))-36

It was working well, but now that there is an empty column the averages are incorrect. If I delete that week, it goes back to calculating averages correctly, but that really isn't viable as then future weeks don't line up with where they are expected to be.

 

[Jeffrey Mahoney]

Use AVERAGEIF('Players and Scores'!B3:AA3,">0")

AVERAGE will ignore blank cells, but it won't ignore zeros.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[joeblow7]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It is 365.

 

[Fluff]

Thanks for that.
At the moment your formula is averaging the last 4 columns, not the last 5 is that what you want?
Also if you have a blank column in (say) K3 with the last column M3, what should be averaged?

 

[joeblow7]

Thanks for that.
At the moment your formula is averaging the last 4 columns, not the last 5 is that what you want?
Also if you have a blank column in (say) K3 with the last column M3, what should be averaged?

It should average the last 5 values entered in the row, if there are less than 5 rounds for someone than it will average how many they have played. The number of blank columns should have zero effect on the calculation just read the last 5, so in your example; average M3, L3, J3, I3, and H3, assuming there is a value in each of those columns.

 

[Fluff]

Ok, how about

=LET(f,FILTER('Players and Scores'!B3:AA3,'Players and Scores'!B3:AA3<>""),AVERAGE(INDEX(f,SEQUENCE(,5,COLUMNS(f),-1)))-26)

 

[joeblow7]

=LET(f,FILTER('Players and Scores'!B3:AA3,'Players and Scores'!B3:AA3<>""),AVERAGE(INDEX(f,SEQUENCE(,5,COLUMNS(f),-1)))-26)

That isn't working for me. Lets use the second row as an example. The last 5 were 42, 48, 45, 42, and 42. They average to 43.8, and then I subtract 36 from it since that is par for 9 holes. It should give a current handicap of 7.8. Using that formula it gives an answer of 9.4. Using my original formula it gives an answer of 8.25.

 

[Fluff]

There's a typo the formula is subtracting 26 rather than 36.

 

[joeblow7]

There's a typo the formula is subtracting 26 rather than 36.

I saw that and tried with 36, but made it even worse. For the second row it gave an answer of 6, and there are multiple rows with -36 as the value.