Golf Handicap Formula help

[Gulfcourse]

I took over the league this year and was given a CD for a golf program that worked with Windows Caveman so I decided to create my own since it is such a small league of old guys!
To establish a handicap for this season I manually enterd 5 scores in Columns I through N, in every row, to get a handicap to reflect the one they finished with last season.
I have been entering their scores twice a week since April and am currently in Column BB.
Not everyone plays every time so there are gaps in some players rows.

I have had a working formula to take the last 5 scores to establish the golfers handicap but it has gone wonky on me.
The formula listed below is in column CA5 through CA28.
The number from Column CA is returned to the corresponding player in Column D and then the other simple formulas, in Columns D, E, & F kick in to figure the handicap.
The problem is the totals in Column CA were no longer consistent, some were only totaling up the last 4 scores instead of 5.
The original formula is:
=IFERROR(SUM(OFFSET($H10,0,LARGE(IF(I10:BY10<>"",COLUMN(I10:BY10))-8,$BZ$3),1,(45-LARGE(IF(I10:BY10<>"",COLUMN(I10:BY10))-8,$BZ$3)))),"NOT ENOUGH")
but for some rows it is only counting the last 4 and I've had to go to those rows and change the -8's in the formula to 9's or 10's to get 5 scores and I have no idea why.

The players are in the rows and the course scores are in the columns.
I have a drop down to pick a value in BZ3 and leave it at 5, this enables me to change the number of scores I want to use but I have not changed it from 5.

Some folks have more rounds than others but I don't think that's the problem since, for example, Petro was correct in CA22 and one row down Roderick CA23 was incorrect until I changed Roderick's 8's to 9's.
The formula (the original) in for Petro that returns 5 is
=IFERROR(SUM(OFFSET($H22,0,LARGE(IF(I22:BY22<>"",COLUMN(I22:BY22))-8,$BZ$3),1,(45-LARGE(IF(I22:BY22<>"",COLUMN(I22:BY22))-8,$BZ$3)))),"NOT ENOUGH")

The formula I had to change to get 5 scores for Roderick is:
=IFERROR(SUM(OFFSET($H23,0,LARGE(IF(I23:BY23<>"",COLUMN(I23:BY23))-9,$BZ$3),1,(45-LARGE(IF(I23:BY23<>"",COLUMN(I23:BY23))-9,$BZ$3)))),"NOT ENOUGH")

I don't want to have to constanly change the formulas so any suggestions would be greatly appreciated!

 

[Peter_SSs]

I pasted =ISNUMBER(BC5) into BD9 and it returned TRUE.

Thanks.

I think that I didn't make the other part clear enough though. When I said use =ISTEXT(BY5) I did not mean to put that formula in BY5, just point it at BY5.
So put it in say BY1 or some other empty cell.

 

[Gulfcourse]

And a possible fault of mine as well might be;
I manually entered the scores in I3:K28 and then started to PASTE VALUES in from another page in workbook, perhaps that's where the circular reference was coming from?