Wednesday, June 11, 2014

yogi_Compute Average Of Last Specified Number Of Non-Zero Responses in 'Days To Response' Column

                                         Google Spreadsheet   Post  #1665
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun-11-2014
post by DB Spork: (!mydiscussions/docs/oT0N3WNKHGM)
Average last 5 non-zero entries in a column above the current row
Hi everyone,

In my sheet I have column H containing "days to response" data. Some rows have been responded to and have a value like 43, while others have not been responded to and have a 0. (If it's easier I can change it to return "" rather than 0 for items not responded to if that makes the formula easier)

In column J I would like to show the average of the last 5 responses, which means averaging the last 5 non-zero entries above the current row in column H. ie: if this is on row 63 it will look at H2:H63. 
I want to cascade this formula down the entire sheet so each row calculates the average of the previous 5 values in H.

In doing some research I found the following threads but I'm having trouble piecing them together or getting them to work.!category-topic/docs/how-do-i/gfOBsk8jQ4Y!topic/docs/Iu4gPSU_mU0!msg/docs/zsqJ-ALWDeM/aMWQEylP0S8J

Thank you in advance for any help!