Averaging or Counting a number of cells in a column

[madchemist]

I have a spreadsheet I am using to calculate golf handicaps. Columns B through Y contain the weekly scores for each player. Not everyone plays each week so there will be blank cells, but what I am trying to do is average the 8 most recent scores, so essentially start at Column Y for each player and come up with the average score of their last 8 rounds. So for some players it might be 8 cells immediately preceding (and including) column Y. Others might have missed weeks so it needs to go back further until it finds 8 cells with values in them.

Anyone have any ideas how I can accomplish this? Ideally I'd want to average the last 8 scores in column Z.

 

[madchemist]

This seems to work. Not sure if this can be simplified, but I also wanted to remove the max and min from the last 8 rounds before averaging.

=(SUM(IF(COLUMN(B42:V42)>=LARGE(IF(ISNUMBER(B42:V42)|COLUMN(B42:V42))|MIN(8|COUNT(B42:V42)))|IF(ISNUMBER(B42:V42)|B42:V42)))-MAX(IF(COLUMN(B42:V42)>=LARGE(IF(ISNUMBER(B42:V42)|COLUMN(B42:V42))|MIN(8|COUNT(B42:V42)))|IF(ISNUMBER(B42:V42)|B42:V42)))-MIN(IF(COLUMN(B42:V42)>=LARGE(IF(ISNUMBER(B42:V42)|COLUMN(B42:V42))|MIN(8|COUNT(B42:V42)))|IF(ISNUMBER(B42:V42)|B42:V42))))/(COUNT(IF(COLUMN(B42:V42)>=LARGE(IF(ISNUMBER(B42:V42)|COLUMN(B42:V42))|MIN(8|COUNT(B42:V42)))|IF(ISNUMBER(B42:V42)|B42:V42)))-2)

 

[a massive zebra]

Wouldn't this be simpler?

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":Y2"))

Entered into cell Z2 using CTRL+SHFT+ENTR, not just enter.

 

[RickXL]

Hi "a massive zebra",

NB I am not the original poster.

I looked at this but I have the wrong sort of mind. I can't do anagrams either!

So ray:

However, it would be even better if the final "Y2" was not hard-coded so you could drag it down to other rows.

 

[a massive zebra]

Rick,

This formula could be copied down:

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":"&ADDRESS(2,COLUMN()-1)))