I have a spreadsheet that calculates handicap changes - I would like script (if possible) to do the following:
The first step is that the total cell (for each row) picks up the last 3 scores if they qualify
A formula will then decide if a handicap change is due. However if a handicap is changed they are allowed 3 ore scores before their handicap is reviewed again.
In the example below I would need to add the 3 scores for Alan and then find the average of those 3 scores (total divided by 3) - but Victor is allowed one more score before he qualifies for a handicap review - at present we colour code yellow for the scores that qualify for handicap review and purple for those that don't (so not sure if calculations can be determined by colour).
I would like the total in Column AL and if possible the number of scores included in Colum AK. So in the example below:
Victor would show 2 in Column AK and nothing in Column AL
Alan would show 3 in Column AK and 50 in Column AL (150 /3)
<tbody>
</tbody>
The first step is that the total cell (for each row) picks up the last 3 scores if they qualify
A formula will then decide if a handicap change is due. However if a handicap is changed they are allowed 3 ore scores before their handicap is reviewed again.
In the example below I would need to add the 3 scores for Alan and then find the average of those 3 scores (total divided by 3) - but Victor is allowed one more score before he qualifies for a handicap review - at present we colour code yellow for the scores that qualify for handicap review and purple for those that don't (so not sure if calculations can be determined by colour).
I would like the total in Column AL and if possible the number of scores included in Colum AK. So in the example below:
Victor would show 2 in Column AK and nothing in Column AL
Alan would show 3 in Column AK and 50 in Column AL (150 /3)
| Victor Lewis | 60.0 | 57.1 | 67.0 | 40.0 | 75.0 | |||||||
| Alan Carmel | 60.0 | 52.0 | 60.0 | 33.3 | 50.0 | 75.0 | 25.0 |
<tbody>
</tbody>
