How to get the average of the last four values of a row

Question

I have a spreadsheet that looks like this.

It basically tracks the ratings of each show.

As you can see each row as a varying number of values. What I want to be able to do is to get the avg for each show for the last 4 ratings only.

For example, for the show 30 Rock, I would want to get the values in column L, M, N and O for that row and then divide by 4. I'm looking for a formula that I can apply to all shows rather than manually updating the formula each time.

Notes:

• The formula would also need to take into account some shows that don't have any values yet or shows that have less than 4 values entered.
• We have a maximum of 24 values that can be entered for any one show, i.e. columns C to Z if that helps.

Answer 1

Try using OFFSET to get the range and AVERAGE function to average, i.e. like this for row 2:

=iferror(average(offset(C2,0,max(0,count(C2:Z2)-4),1,4)),0)

If there are fewer than 4 values, that will average them all, otherwise just the last 4—if there are no values you get 0. You can change that to a blank by replacing 0 at the end of the formula with "".

This formula works in both Excel and Google Spreadsheets.