Calculate Average of First Lowest Scores over a Timespan

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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?

    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!

Register Yourself Login