Running average problem

____A______B_____C________D
1__4.305____1___4.305____4.305
2__4.305____1___4.305____4.305
3__4.305____0___
5__3.95_____0___
6__4.005____1___4.005____4.0525
7__4.005____1___4.005____4.005


I have data as such in columns A,B, and C; actually a small sample of several thousand points. Anyway, for column D, here is what I would like returned:

Anywhere there is a 1 value in B (example: row 6), I want D to look back at the previous rows for 0 values in column B until it hits a 1 (rows 3, 4, and 5 all contain 0s, but B2 contains a 1), then take an average of corresponding values in A. In the above sample D6 equals the average of A3 trhu A6 (D6=[A3+A4+A5+A6]/4) or (4.0525=[4.305+3.95+3.95+4.005]/4). Notice D7=A7 because B6=1. So D7 looks back at B6, sees that B6=1, therefore returns A7. Similarly D2=A2 because B1=1.


A problem with my data set is that there may be a bunch of 0s consecutively in column B, and sometimes there are a bunch of 1 consecutively. This seems to me like it would take multiple steps to solve. I am not familiar with macros, but if that's what it takes I'd be willing to try to learn.

I hope someone understands my explanation and can give me an answer! Thanks in advance.

You get better help if you add an excel file with your data (without confidential information)

ctrap, forum rules require you to wait at least 24 hours befor bumping. Also, some members only view threads with 0 responses, this thread now had 2

@ctrap33

Please Login or Register  to view this content.

Please reply to whom you are replying.

Insert a blank line above your data, and then in cell D2, array enter the formula

=IF(C2=0,"",IFERROR(AVERAGE(IF(ROW($B$2:B2)>(MAX(IF($C1:C$2=1,ROW($C1:C$2)))),$B$2:B2)),B2))

and copy down to match your data set.

Here's your modified file.

With formula.xlsx