Excel help please

[Affo]

Hi all,

Working on handicaps and a sort of US version (without the slope ratings), but using the best 10 of last 20 scores and less if less rounds are played.

I have limited scores used to last 6 months, if someone hasn't played an event in last 6 months, they would have to start afresh.

2 problems/issues.

In column E the formula (below) doesn't use the last 20 scores unless I change the number in the formula to 24 (in red). If I use 20, it uses the last 16 entries. If I use 24, the formula throws up an error if a players no rds is 21,22 or 23 in column C.

Any ideas?

=IF(COUNT(F5:ZY5)=0,"",IF(COUNT(F5:ZY5)<20,AVERAGE(F5:ZY5),AVERAGE(SMALL(ZY5:INDEX(F5:ZY5,LARGE(IF(F5:ZY5<>"",COLUMN(F5:ZY5)-1),24)),{1,2,3,4,5,6,7,8,9,10}))))

The 2nd issue is if someone has played less than 20 rounds, then less rounds and best ofs are used:

Total of Rounds Rounds to use for Average
0 0
1 0
2 0
3 0
4 0
5 1
6 1
7 2
8 2
9 3
10 3
11 4
12 4
13 5
14 5
15 6
16 6
17 7
18 8
19 9
20 10

Is there a way to input into the formula above something that changes the "lowest" numbers (1,2,3,4,5,6,7,8,9,10) to (1,2,3,4) for someone who's played 11 or 12 rounds for example? I'm not convinced it can be done but there are some clever people out there.

Attached is a snippet of the sheet.

upload.xlsx

(113.85 KiB) Downloaded 80 times

Thanks in advance.

Andy

[Affo]

seems a brain box on excel forum has managed to solve this for me. watch this space (well, not this particular one, but on the forums) :)

[Armand]

I haven't looked at your formula in any detail, but I created a sheet that only looked at the last "X" number of rounds to calculate an average/handicap that I could share. It seems you have resolved the problem, though, so I probably won't look at it any further.