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.
How to get the average of the last four values of a row
google spreadsheets
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 4if 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.