+ Reply to Thread
Results 1 to 6 of 6

Average the first 10 non blank cells across a row

  1. #1
    Registered User
    Join Date
    01-14-2020
    Location
    Philadelphia, USA
    MS-Off Ver
    2007
    Posts
    2

    Average the first 10 non blank cells across a row

    I've seen similar posts on this topic and found a few that work fine when using the array formula to do this calculation down a column but all my efforts to do it across a row will not provide exact results.

    I'm attempting to average each players last 10 weeks scores. It doesn't matter if they play every week, I just need the scores of their last 10 outings averaged.

    In this example, Column (B) uses the average function and manually selected columns with data.
    I have two other columns where I've attempted using:
    OFFSET Column (C)
    =AVERAGE(OFFSET(F4,0,0,1,SMALL(IF(ISNUMBER(F4:BC4),ROW(F4:BC4)),10)))

    and INDEX Column (D)
    =AVERAGE(F4:INDEX(F4:BC4,LARGE(IF(F4:BC4<"",COLUMN( F4:BC4)),MIN(COUNT(F4:BC4),10))))

    The results are usually pretty close to the actual average, but that isn't going to cut it.

    Any help would be greatly appreciated
    Hopefully the attachment comes across.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-28-2008
    Location
    Turkey
    Posts
    88

    Re: Average the first 10 non blank cells across a row

    May be formula like this:


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Not: Change ; with ,

    Array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    .
    Last edited by yurttas; 01-16-2020 at 03:22 PM.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Average the first 10 non blank cells across a row

    Hello and welcome to the forum.

    Try this:

    =AVERAGE(F4:INDEX(F4:AB4,SMALL(IF(ISNUMBER(F4:AB4),COLUMN(F4:AB4)),MIN(A4,10))-COLUMN(F4)+1)) Ctrl Shift Enter
    Last edited by 63falcondude; 01-16-2020 at 03:23 PM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average the first 10 non blank cells across a row

    Another possible also array entered
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Registered User
    Join Date
    01-14-2020
    Location
    Philadelphia, USA
    MS-Off Ver
    2007
    Posts
    2

    Smile Re: Average the first 10 non blank cells across a row

    Each of these responses works accurately and each with their own benefits.
    I really appreciate the assistance and rapid response.

    Thank you all very much!!!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average the first 10 non blank cells across a row

    You are welcome. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to average a value in blank cells in a column between cells with data
    By mitomke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2019, 10:50 AM
  2. Average non-contiguous cells, ignore blank cells and avoid #DIV/0!
    By Davdef in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2018, 10:03 AM
  3. Replies: 10
    Last Post: 04-25-2013, 06:12 PM
  4. Average the last 10 cells of a dynamic column but ignore blank cells & 0
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2013, 11:37 PM
  5. [SOLVED] Average of last n number of cells NOT including blank cells
    By dsklein85 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2013, 03:06 PM
  6. [SOLVED] find blank cells in column and average the cells below
    By desibabuji in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2013, 12:03 PM
  7. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1