Average of last 10 non-blank cells

  • Thread starter Thread starter Peter Goergen
  • Start date Start date
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
 
Hi Pete
do you have blank cells in between. If not use
=AVERAGE(OFFSET($A$1,0,COUNTA(1:1)-1,1,-10))
 
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...
 
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
 
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...
 
Back
Top