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

1. ## 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!

2. ## 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

3. ## 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. ## Re: Calculate average of last 10 cells in a row that have a value, skipping blanks

another approach ..array formula average(if..

Starts in Cell B33

Golf Handicap For Help.xlsx

5. ## 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. ## 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

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

#### 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