Average lowest 5 of last 6 golf scores in a column

[dgeorge]

I'm having difficulty with the formula to find the lowest 5 of the last 6 golf scores listed in a column. The scores are auto-populated from another sheet so each cell with a score already has a formula in it. I will also have weeks when someone doesn't play so blank cells are common. My scores are entered in B7:B21, C7:C21, and so on. I'd like this formula to be in B27 but can put it elsewhere if necessary.

I created an example to test a formula found on the internet. In the example, scores are listed in a single row, A1:J1, with one cell blank and this formula works fine but trying to convert this to work in a single column has proven difficult. The -35.5 at the end is the average par on our course, front 9 is 36, back 9 is 35.
{=IF(COUNT(A1:J1)<6,B23,(SUM((J1:INDEX(A1:J1,LARGE(COLUMN(A1:J1)*(A1:J1<>""),6))))-LARGE((J1:INDEX(A1:J1,LARGE(COLUMN(A1:J1)*(A1:J1<>""),6))),1))/5)-35.5}

Is there any way to convert this formula to work in a column?

Any help would be greatly appreciated!
Thanks!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(SMALL(IF(ROW(B7:B21)>=LARGE(IF(ISNUMBER(B7:B21),ROW(B7:B21)),MIN(6,COUNT(B7:B21))),IF(ISNUMBER(B7:B21),B7:B21)),ROW(1:5)))

 

[dgeorge]

I believe that did the trick. Thanks so much!!

 

[Aladin Akyurek]

I believe that did the trick. Thanks so much!!

You are welcome.