Calcuating total of last 3 cells if they qualify

[marcidee]

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)

[TABLE="width: 800"]
<tbody>[TR]
[TD="width: 219, bgcolor: transparent"]Victor Lewis
[/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: yellow"]60.0
[/TD]
[TD="width: 70, bgcolor: yellow"]57.1
[/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: yellow"]67.0
[/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: #E6B8B7"]40.0
[/TD]
[TD="width: 70, bgcolor: #E6B8B7"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: #E6B8B7"]75.0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Alan Carmel
[/TD]
[TD="bgcolor: #E6B8B7"]60.0
[/TD]
[TD="bgcolor: #E6B8B7"][/TD]
[TD="bgcolor: #E6B8B7"]52.0
[/TD]
[TD="bgcolor: #E6B8B7"][/TD]
[TD="bgcolor: #E6B8B7"][/TD]
[TD="bgcolor: yellow"]60.0
[/TD]
[TD="bgcolor: yellow"]33.3
[/TD]
[TD="bgcolor: yellow"]50.0
[/TD]
[TD="bgcolor: yellow"]75.0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]25.0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]