Average of last 10 non-blank cells

P

Peter Goergen

How do I calculate the average of the last (rightmost) 10 non-blank cells in
a row of numbers and blank cells?

Thanks,
Pete
 
F

Frank Kabel

Hi Pete
do you have blank cells in between. If not use
=AVERAGE(OFFSET($A$1,0,COUNTA(1:1)-1,1,-10))
 
P

Peter Goergen

Yes, unfortunately there are blanks between.

Frank Kabel said:
Hi Pete
do you have blank cells in between. If not use
=AVERAGE(OFFSET($A$1,0,COUNTA(1:1)-1,1,-10))
Click to expand...
 
F

Frank Kabel

Hi
so then try the following array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(OFFSET($X$1,0,0,1,-(COLUMN($X$1)-LARGE(IF(ISNUMBER($A$1:$X$1),
COLUMN($A$1:$X$1)),10)+1)))

Note: this will create an error if you don't have at least ten values
in the range A1:X1
 
P

Peter Goergen

Perfect !

Thanks


Frank Kabel said:
Hi
so then try the following array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(OFFSET($X$1,0,0,1,-(COLUMN($X$1)-LARGE(IF(ISNUMBER($A$1:$X$1),
COLUMN($A$1:$X$1)),10)+1)))

Note: this will create an error if you don't have at least ten values
in the range A1:X1
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Next-To-Last, Non-Blank, Rightmost Value in a Row 6
average in non-continuous set, excluding blanks 6
Excel Average dates help 0
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
formula for days between completed meetings 4
Find the lowest4 numbers of the last 6 in a range 8
How to reference last non-blank cell value from a column cells? 2
Average non-consecutive cells excluding zero 8

Top