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.

 

[Fluff]

I get 7.8 with that formula

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3	a	50	49	42	42	45	48		42
4
5		7.8
6
Data

Cell Formulas
Range	Formula
B5	B5	=LET(f,FILTER(B3:AA3,B3:AA3<>""),AVERAGE(INDEX(f,SEQUENCE(,5,COLUMNS(f),-1)))-36)

Are those cells hard values, or formulae?

 

[joeblow7]

The values are populated from VBA code, but once populated they act like hard coded.

 

[joeblow7]

Realized I had a column of 0 while testing something else and that was affecting the results. It does seem though that it is working, but only if they have at least 5 rounds played. If they have less than it gives an answer of #VALUE!

 

[Fluff]

That's because the columns you said were blank aren't. They contain a 0.
Should all zeros be ignored?

 

[joeblow7]

No that was a mistake on my end I just forgot to remove it. I added the if statement back in for if less than 5 scores and it looks like it is working correctly now. Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.