I'm trying to write a formula or, more likely, a macro (am using Excel 2010) that looks down a column of data and counts the number of consecutive zeros until it reaches a non-zero value, then averages the non-zero value over that range of cells. For example suppose in column B reading down I have 0,0,0,4 then the read down until you reach number 4 (in cell B4) then average the 4 over the preceeding zero cells AND the cell with the 4 in, so 0,0,0,4 would become 1,1,1,1.
0,0,7 would become 2.3, 2.3, 2.3 etc
Also ignore any non-zero cells that are not preceded by zeros, e.g. 1,1,0,0,7,1,1 becomes 1,1,2.3,2.3,2.3,1,1 (i.e. the 1's are left alone).
I have attached a spreadsheet showing the data format I am working with. Column A is a time-date stamp and B is the data. Hopefully this explains better what I am trying to do. Any help appreciated in getting started with this.