excel - Time Lookback Calculation -

excel - Time Lookback Calculation -

i have 2 columns:

column a: time (seconds midnight)

column b: value (arbitrary value i've created)

how find average value (column b) @ instance in time(row) looking on previous x seconds (column a)?

lets assume a1 = seconds after midnight

seconds after midnight value 0 27 2 29 6 2 16 29 20 19 24 4 34 2 40 1 44 4 54 12 64 12 71 3 81 30 91 21 92 1 93 27 97 12 104 30 112 25

note time deltas variable can't @ lastly x rows.

a specific question be:

in new column (column c) homecoming average value of lastly 10 seconds of data.

i have no thought how this. can help? it'd appreciated.

edit:

the output in first 4 rows of column c be:

seconds after midnight value result 0 27 no values prior 1 2 29 27 average(b2) 6 2 28 average(b2:b3) 16 29 2 average(b4)

for each row, i'm taking current time (16 in lastly case)...going x seconds (10 seconds in case) , averaging values cells in time range (not including current value).

my main issue time calculation. don't know how calculate roll forwards in time , go on more instances move forward. if go 10 seconds without new info point there no output since. if got 1 instance in previous 10 seconds output value. if got 100 instances in previous 10 seconds, need homecoming average of 100 instances.

again, i'd appreciate help, hints, links. driving me crazy.

try next formula in cell c3:

=iferror(sumifs(b:b,a:a,"<"&a3,a:a,">="&a3-10)/countifs(a:a,"<"&a3,a:a,">="&a3-10),0)

sumifs sum of values seconds are:

less a3 (i.e. less 2 in case) more or equal a3-10 (i.e. above -8 in case)

and sum divided number of lines found same criteria.

if there error (more when countifs returns 0, #div/0!) 0 instead of error.

excel spreadsheet