Calculating Handicaps

I run a disc golf league and track scores in an excel spreadsheet. You have to play 3 times, then get your handicap. I need to know how to set up my spreadsheet so that each week as I enter scores in the rows for each player, excel will take the last 3 scores in each row and add them together, divide by 3 (in other words, average the last 3 scores) subtract 49 from the average and then multiply that number by .8, rounding up to the nearest whole number.

Example: 55 + 51 + 52 = 158 divided by 3 = 52.66666667 minus 49 = 3.666666667 X .8 = 2.93333333 round up, 3 would be the handicap.

I need the formula to automatically use just the last 3 scores in each row to do the calculation. Thank you!

This is just an example of what I'm talking about. Not everyone will play every week so there may be some weeks with no score entered and again, just need it to use the last 3 scores available to calculate the handicap.


Name Week 1 Week 2 Week 3 Handicap Week 4 Handicap Week 5 Handicap Week 6 Handicap Week 7 Handicap
Barry 51 49 53
Russ 48 45 52
Sam 50 52
Adam 52 54
Tom 53 51 56
AJ 52 51
Mike 50 55 50
Dan 59 56 52

Hi,

Try this

=ROUND((AVERAGE(OFFSET($B$1,COUNT(B:B)-3,0,3)) -49)*0.8,0)