Tuesday, February 12, 2013

yogi_Find The Average Of Last Specified Number Of Values In A Row

                                          Google Spreadsheet   Post  #1026
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 11, 2013
user DarkUFO said:(http://productforums.google.com/forum/?zx=6rslubc1d2of#!category-topic/docs/spreadsheets/tO9wQMXh3FI)
How to get the average of the last 4 values of a row

I was wondering if someone could help me with a formula. I've wracked my brain and can't think of a way of doing this.

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.
eg 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.
Note: 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.
Note: We have a maximum of 24 values that can be entered for any 1 show
Any help would be greatly appreciated. ie columns C to Z if that helps.
following is a solution to a bit more generalized problem