Hi All,

Apologies first, I am new to the forum and a novice at excel......

I am trying to create a basic workbook to track scores for my shooting club and include a formula that will workout our handicaps.

we have 9 shoots over a year, all out of 100 so its pretty straight forward to track the totals hit and missed, however, the complication comes from the formula for the handicap.

A members handicap is 2/3 of what they have missed over the last 5 shoots, up-to a maximum of 40 e.g. for simple maths, if a member hits 50 out if 100 over the last five shoots, there handicap would be as follows:

250 (shots missed) ÷ 5 (last 5 shoots) = 50
50 ÷ 3 = 16.6
16.6 x 2 = 33.3

if possible, I would like to round this number up.

in addition, 0 (zero) values need to be ignored as not every member attends every shoot.

My main problem has been formulating a simple way for the handicap to only include the last 5 shoots as each month the formula will change i.e. when a shooters score is added in to the column for the 6th shoot, the handicap formula must discount the first month, then for the 7th shoot discount the first and second month etc. etc.

I have attached a spread sheet that I have been playing with:

Handicaps.xlsx

Any help would be greatly appreciated.

Thanks.