How do I average the 3 lowest of the first 4 scores say in a 10 week span since not all players will be there the first 4 weeks?
Calculate Average of First Lowest Scores over a Timespan



Re: Calculate Average of First Lowest Scores over a Timespan
The answer here depends on your setup. Are the scores in a row or a column, are the first scores at the top of the bottom (or left or right). If the player isn't there what goes in the cell for that week, is it left blank or filled with zero, text or something else?
Assuming you have scores in B2:K2 with week 1 score in B2 and blanks where the player isn't there try this
=SUM(SMALL(B2:INDEX(B2:K2,SMALL(IF(B2:K2<>"",COLUMN(B2:K2)COLUMN(B2)+1),4)),{1,2,3}))/3
That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER
You'll get an error if there are less than 4 numbers  what should happen then, e.g. if there are only 2 or 3 scores?

Re: Calculate Average of First Lowest Scores over a Timespan
daddylonglegs
You are fantastic! All the different functions I tried with round about ways that didn't work. Yours worked perfectly....scores are in a row, weeks not played are blank, scores start at left, B2. You even picked the layout I have. Thanks much again.....
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!