Hello.
I need to know if there's a way to return the average of the last 3 cells in
a row, no matter how many times values are added to the row.

Thanks.

Assuming all of the data is numeric and there are no gaps

=AVERAGE(OFFSET(A1,0,COUNT(1:1)-3,1,3))
would return the average of the last 3 numbers in row 1.

"thorshammer" wrote:

Hello.
I need to know if there's a way to return the average of the last 3 cells in
a row, no matter how many times values are added to the row.

Thanks.

This formula will average the last 3 numbers in row 1, even if there are
gaps. If there are less than 3 numbers it averages them all

=AVERAGE(INDEX(1:1,LARGE(IF(ISNUMBER(1:1),COLUMN(1 :1)),MIN(COUNT(1:1),3))):IV1)

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

"JMB" wrote:

Assuming all of the data is numeric and there are no gaps

=AVERAGE(OFFSET(A1,0,COUNT(1:1)-3,1,3))
would return the average of the last 3 numbers in row 1.

"thorshammer" wrote:

Hello.
I need to know if there's a way to return the average of the last 3 cells in
a row, no matter how many times values are added to the row.

Thanks.

Just to round things out, these will round the last three values in a COLUMN:
=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-2,0,3))

=AVERAGE(OFFSET($A$1,COUNTA($A:$A)-3,0,3,1))

=AVERAGE(INDEX(A:A,MATCH(10^10,A:A)-2):INDEX(A:A,MATCH(10^10,A:A)))

=AVERAGE(OFFSET(A1,MATCH(9.99999999999999E+307,A:A )-3,,3))

Now, you have it both ways!!

Regards,
Ryan---


--
RyGuy


"daddylonglegs" wrote:

This formula will average the last 3 numbers in row 1, even if there are
gaps. If there are less than 3 numbers it averages them all

=AVERAGE(INDEX(1:1,LARGE(IF(ISNUMBER(1:1),COLUMN(1 :1)),MIN(COUNT(1:1),3))):IV1)

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

"JMB" wrote:

Assuming all of the data is numeric and there are no gaps

=AVERAGE(OFFSET(A1,0,COUNT(1:1)-3,1,3))
would return the average of the last 3 numbers in row 1.

"thorshammer" wrote:

Hello.
I need to know if there's a way to return the average of the last 3 cells in
a row, no matter how many times values are added to the row.

Thanks.