+ Reply to Thread
Results 1 to 6 of 6

Calculate average of last 10 cells in a row that have a value, skipping blanks

  1. #1
    Registered User
    Join Date
    10-20-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Calculate average of last 10 cells in a row that have a value, skipping blanks

    I am working with a dataset involving golfers' scores, arranged with each golfer's name in column A and the associated scores in columns B-AG of each row. I will be continuously adding data to columns to the far right (as I have more scores). I want to calculate the average of the last 10 scores (cells), but only including cells that have a value, meaning the formula will need to skip cells that are blank.

    I'm attaching a workbook with the dataset.

    Any suggestions would be much appreciated!

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

    Hello and Welcome to the forum! You mean to say formula will calculate the average of last 10 cells only and while calculating the average of last 10 cell and should not include blank cells. Like if 5 out of 10 are blank then it should sum 5 and average of 5 cells
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

    Try this in AI 1
    =IFERROR(ROUND(SUM(INDEX(A1:AH1,1,COLUMNS($A$1:AH1)-11):INDEX(A1:AH1,1,COLUMNS($A$1:AH1)))/COUNT(INDEX(A1:AH1,1,COLUMNS($A$1:AH1)-11):INDEX(A1:AH1,1,COLUMNS($A$1:AH1))),2),"")

    Whenever you are adding new column select AH and insert
    Hope this helps

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

    another approach ..array formula average(if..
    please look attachment for details.

    Starts in Cell B33

    Golf Handicap For Help.xlsx
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    10-20-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

    To clarify, I need to calculate the average of the last 10 scores for each person, regardless of how many cells that would take. Meaning for some it might be the last 10 columns, but some may have blanks and therefore require an average of the last 15 cells (if 5 are blank) to determine the average of the last 10 scores/cells that have a value. So it is not merely the average of the last 10, but the last 10 excluding blanks.


    Thanks!

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

    Find the attached

    Formula entered in the sheet is an array formula, if you make any change hold control and shift then hit enter to make it array formula
    Attached Files Attached Files

+ 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. [SOLVED] Copy cells from one worksheet to another while skipping blanks
    By gawk1980 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-23-2013, 05:02 PM
  2. [SOLVED] Combining data from multiple cells while skipping blanks
    By htek9 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-17-2012, 03:21 PM
  3. UDF to calculate average according to condition, ignoring blanks
    By dmitry in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-14-2011, 05:51 PM
  4. Subtraction formula for consecutive cells in a column, skipping blanks
    By elton@rogers.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-15-2005, 08:05 PM
  5. [SOLVED] Paste Special Skip Blanks not skipping blanks, but overwriting...
    By gsrosin in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 12:06 AM

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