Take the 2-minute tour ×

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.


  • 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.
share|improve this question

migrated from superuser.com Feb 12 '13 at 18:12


1 Answer 1

up vote 4 down vote accepted

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


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.

share|improve this answer
Wow, that's awesome. Thank you so much. –  DarkUFO Feb 12 '13 at 20:26

Your Answer


