Forum Discussion

Ray22300's avatar
Ray22300
Copper Contributor
Nov 02, 2025

Golf league form

I keep each week's score on a excel spread sheet I developed, and I have to do meltable inputs each week. One is the score. I have 28 weeks of play and form input. All works well except for when I input the current week the form is set up so the last 3 rounds are what your handicap is fingered on. So, week one thru 3 will be your starting handicap. Week 4 input is made, and I have to delete field 1 so as to still only have 3 numbers that the handicap figures from. The form is setup for 28 weeks, but the handicap is only 3 weeks at a time. Is there a formula that will auto drop the last uncounted score automatically. For example, field f1, g1, h1, would be your 1st 3 scores that establish your handicap to start the session the 4th round would be g1, h1, and i1. How can I auto delete f1, so I don't have to manually do it every time.

 

                                  
Formulas and Functions

2 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Nov 03, 2025

    don't delete it just use the formula to take the last 3.  For example if the whole range is F1:AG1 and all the 'future' weeks are empty (as in no data, no number, no formula, no text) then an easy way is to use:

    TAKE(F1:.AG1,,-3)

    NOTE there is a period after the colon and before AG, that is a shortcut for the funtion TRIMRANGE which will remove empty cells (form beginning, end or both depending on how you set it up).

    also note there are 2 commas because you want the columns (not rows)

    so this formula looks at the range F1:AG1 and 'trims' it down to where there is data then take the first 3 columns from the END (hence the negative 3)

    now you can take the average like: =AVERAGE(TAKE(F1:.AG1,,-3))

    Hope that helps

  • korinakats's avatar
    korinakats
    Copper Contributor
    Nov 03, 2025

    Hi Ray22300,

    I can help with this! You need a formula that automatically keeps 
    only the last 3 scores for the  calculation.

    Here's the solution:

    You can use a combination of OFFSET and COUNTA to always reference 
    the last 3 entries:

    For the 3rd last score:
    =IF(COUNTA($F$1:$AG$1)>=3,OFFSET($F$1,0,COUNTA($F$1:$AG$1)-3),"")

    For the 2nd last score:
    =IF(COUNTA($F$1:$AG$1)>=3,OFFSET($F$1,0,COUNTA($F$1:$AG$1)-2),"")

    For the last score:
    =IF(COUNTA($F$1:$AG$1)>=3,OFFSET($F$1,0,COUNTA($F$1:$AG$1)-1),"")

    This will:
    1. Count how many scores you have (COUNTA)
    2. Always grab the last 3 scores
    3. Auto-update as you add new weeks

    For your specific case with f1, g1, h1, i1:
    - Place this formula where you calculate 
    - It will automatically use g1, h1, i1 (last 3) when you add i1

    Best,
    Korina