Calcuating total of last 3 cells if they qualify

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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.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>
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top