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.

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.