# Thread: Averaging or Counting a number of cells in a column Thanks: 0 Likes: 0

1. ## Averaging or Counting a number of cells in a column

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.

2. ## Re: Averaging or Counting a number of cells in a column

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)

3. ## Re: Averaging or Counting a number of cells in a column

Wouldn't this be simpler?

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

4. ## Re: Averaging or Counting a number of cells in a column

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

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

5. ## Re: Averaging or Counting a number of cells in a column

Rick,

This formula could be copied down:

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•