Average only the last 3 cells in a row and self-update as added

[thorshammer]

I have a spreadsheet where I record individual scores on a daily and weekly
basis. The spreadsheet consists of a column of names down the left side to
which I add new scores in the row next to each name every time someone
competes.

(example)
John 100 80 90 110
Amy 90 70 90 120 80
etc.....

I would like to create a formula which would search down to the end of the
populated row and take the average of ONLY the last 3 cells. Optimally, the
result would 'auto-update' every time I added scores to the end of the row.

Is this possible?

 

[Teethless mama]

=AVERAGE(OFFSET(A1,,COUNT(1:1)-2,,3))

 

[Teethless mama]

Another...

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

 

[Ron Rosenfeld]

I have a spreadsheet where I record individual scores on a daily and weekly
basis. The spreadsheet consists of a column of names down the left side to
which I add new scores in the row next to each name every time someone
competes.

(example)
John 100 80 90 110
Amy 90 70 90 120 80
etc.....

I would like to create a formula which would search down to the end of the
populated row and take the average of ONLY the last 3 cells. Optimally, the
result would 'auto-update' every time I added scores to the end of the row.

Is this possible?

IF your formula is in column B, and data is entered contiguously in each row
(i.e. no blanks) then this should work:

=AVERAGE(OFFSET(B1,0,COUNT(C1:IV1),1,-3))

If there might be blanks that should not be counted, then this **array**
formula should work:

=AVERAGE(TRANSPOSE(INDIRECT(ADDRESS(ROW(),LARGE(ISNUMBER(C1:IV1)*COLUMN(C1:IV1),{1,2,3})))))

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

Once entered into B1, either formula can be "filled-down" and the references
should adjust properly.

As written, the formulas will NOT work if they are not entered in the same row
as the data.
--ron