Calcuating total of last 3 cells if they qualify

marcidee

Board Regular
Joined
May 23, 2016
Messages
193
Office Version
  1. 2019
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]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,222,414
Messages
6,165,892
Members
451,992
Latest member
kaurmanjodh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top