I have a spreadsheet that I use to calculate my golf handicap. It is pretty complicated, and worked until recently.

The error I have is odd. The latest line has has some errors #N/A - but when I add a new line, the error moves to the new last line. Typically, the spreadsheet looks at the last 20 entries on the main page. Actually it is the 20 rows with the most recent dates. I am now at row 335. Periodically, I have to expand the spreadsheet by copying lines down another hundred rows (there should be a better way to do this)

Column B has an "0" if the entry is one of the 10 best rounds of my last 20 rounds of golf. Column J has a calculated value based upon my round's score and the difficulty of the course.

L is hidden and L35 is =IF(AND(F335>='Performance Statistics'!$E$7,F335<='Performance Statistics'!$E$8,C335<21),1/J335+(RAND()/10000),0)When I unhide it, I see #N/A, so I test by replacing it with =1/J335+(RAND()/10000 That calculates correctly, but doesn't fix the other problems.

B shows a number between 1 & 20 going from the latest date to the earliest It is #N/A for the last line:
=IF(AND(F335>='Performance Statistics'!$E$7,F335<='Performance Statistics'!$E$8,G335>0),RANK(F335,$F$4:INDIRECT("F"&$Q$10)),500)

C is a ranking of the corresponding scores
=IF(AND(F335>='Performance Statistics'!$E$7,F335<='Performance Statistics'!$E$8,G335>0),RANK(L335,$L$4:INDIRECT("L"&$Q$10)),500)

'Performance Statistics'!$E$8 has the end date 4/11/29
'Performance Statistics'!$E$7 has the start date 6/20/51

I'm using Excel 2011 for Mac.