Blank rows are interferring with formula result

[retiredjhawk]

#num! and #value! error for same formula was my thread on May 5 and I marked my problem solved by daddylonglegs. Now, however, I realized that the formulas I have been using for several years are not giving me the right answers when I have a blank row. I need to find the golf handicap based on the best 5 scores out of the last 10 but not include blank rows. I hope I will not confuse you with the 3 worksheets. One is the original on May 5 with no blank rows; the other two have blank rows when a golfer is absent. I wanted to check the formulas and found they don't work with a blank row.. In the worksheet titled with blank rows, I fixed the formulas in column O by hand. I don't think I should have to do that. Right? The formula provided by DonkeyOne in column Q doesn't work at the beginning; . In the worksheet marked not changed, O19 should be K9:K19 not K10; 020 should be K10:K20 not K11. Are any of the formulas I have been using right? I also want to record the handicap in column M based on what was determined in column O the week before. It works unless there is a blank row and then zero is recorded. Thanks in advance

[DonkeyOte]

I believe it should read:

Q6:
=IF(OR(SUM($K6)=0,COUNT($K$6:$K6)<5),"",AVERAGE(SMALL(INDEX($K$6:$K6,LARGE(IF(ISNUMBER($K$6:$K6),ROW($K$6:$K6)-ROW($K$6)+1),MIN(10,COUNT($K$6:$K6)))):$K6,{1,2,3,4,5})-37)*0.9)
confirmed with CTRL + SHIFT + ENTER
copied down to Q32

[retiredjhawk]

I must be doing something wrong. I get the error message #NAME? in rows Q10 to Q32 but not in the blank rows. I noticed you did change the formula.

Also, could you please help with the other problem I had: I also want to record the handicap in column M based on what was determined in column O the week before. It works unless there is a blank row and then zero is recorded.

[DonkeyOte]

I must be doing something wrong. I get the error message #NAME? in rows Q10 to Q32 but not in the blank rows. I noticed you did change the formula.

The spurious space strikes again... for some reason the board software inserts spaces after n characters.
The space that appears between ROW and ($K$6:$K6) should not be there - remove it from the formula, confirm the Array in Q6 and then copy down and all should work.

Also, could you please help with the other problem I had: I also want to record the handicap in column M based on what was determined in column O the week before. It works unless there is a blank row and then zero is recorded.

Not entirely sure I follow but if you want to use the last numeric value (prior to current row) from Col O then:

M6:
=IF(COUNT($O$5:$O5),LOOKUP(9.99E+307,$O$5:$O5),"")
copied down

[retiredjhawk]

I removed the space you mentioned but now I get the #NUM! error message. Is there supposed to be a minus sign before ROW to the left of MIN? I'm sorry I'm such a pain.

I also put in the formula for M6 and it works but it removed my black lines that make the box around each number. How do I get them back? Thanks.

[DonkeyOte]

I removed the space you mentioned but now I get the #NUM! error message.

Did you confirm the formula with CTRL + SHIFT + ENTER (not just Enter) ?

I also put in the formula for M6 and it works but it removed my black lines that make the box around each number. How do I get them back?

For simplicity - see attached in which all of the above are in place.