Calcuating total of last 3 cells if they qualify

marcidee

Board Regular
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)

 Victor Lewis 60 57.1 67 40 75.0 Alan Carmel 60.0 52 60 33.3 50.0 75 25.0

<tbody>
</tbody>

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Replies
15
Views
199
Replies
1
Views
661