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*

*Hi,*

*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