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