Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

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

This question came from our site for computer enthusiasts and power users.

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:

=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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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