Averaging or Counting a number of cells in a column
Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Jul 2006
    Posts
    198
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jul 2006
    Posts
    198
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averaging or Counting a number of cells in a column

    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.

  4. #4
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,314
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    RickXL

    Excel 2013 and Windows 10

  5. #5
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averaging or Counting a number of cells in a column

    Rick,

    This formula could be copied down:

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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