Averaging Data Every 20 Seconds

[Geijer85]

I'm in great need of formula to save me an extreme amount of time and angst. I am attempting to average blood vessel dilation every 20 seconds. I have seconds recorded in one column and diameter in another column. The issue I'm having is that time is not recorded consistently.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Time (sec)[/TD]
[TD]Percent Dilation[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1.016[/TD]
[/TR]
[TR]
[TD]2.37[/TD]
[TD]0.678[/TD]
[/TR]
[TR]
[TD]4.438[/TD]
[TD]0.339[/TD]
[/TR]
[TR]
[TD]4.875[/TD]
[TD]1.356[/TD]
[/TR]
[TR]
[TD]6.906[/TD]
[TD]1.016[/TD]
[/TR]
[TR]
[TD]12.156[/TD]
[TD]1.355[/TD]
[/TR]
[TR]
[TD]13.344[/TD]
[TD]0.678[/TD]
[/TR]
[TR]
[TD]14.016[/TD]
[TD]-1.017[/TD]
[/TR]
[TR]
[TD]14.422[/TD]
[TD]1.102[/TD]
[/TR]
[TR]
[TD]17.563[/TD]
[TD]-0.399[/TD]
[/TR]
[TR]
[TD]18.063[/TD]
[TD]1.356[/TD]
[/TR]
[TR]
[TD]19.279[/TD]
[TD]1.017[/TD]
[/TR]
[TR]
[TD]20.828[/TD]
[TD]1.356[/TD]
[/TR]
[TR]
[TD]24.563[/TD]
[TD]1.011[/TD]
[/TR]
[TR]
[TD]29.563[/TD]
[TD]1.069[/TD]
[/TR]
[TR]
[TD]30.375[/TD]
[TD]1.016[/TD]
[/TR]
[TR]
[TD]31.172[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31.938[/TD]
[TD]0.677[/TD]
[/TR]
[TR]
[TD]36.441[/TD]
[TD]0.998[/TD]
[/TR]
[TR]
[TD]39.98[/TD]
[TD]1.110[/TD]
[/TR]
[TR]
[TD]41.353[/TD]
[TD]0.976[/TD]
[/TR]
[TR]
[TD]43.547[/TD]
[TD]1.359[/TD]
[/TR]
[TR]
[TD]44.112[/TD]
[TD]1.234[/TD]
[/TR]
</tbody>[/TABLE]

Please, please help.

Thank you.

 

[Peter_SSs]

Could it be that the seconds and the diameter are calculated with formulas rather than being typed directly?

That shouldn't matter if the results of those formulas are actual numbers, which you have reported they are.

I tried copying those columns to a new sheet and pasting the values only and everything works to perfection! Thank you, Peter!

That's good, but I have sent you a PM as I'm interested in finding out why it was not working already. Check "Private Messages" in the green section at the top left of the page.

 

[markmzz]

I small modification in the Peter's formula:

K2-> =AVERAGEIFS(J$2:J$778,I$2:I$778,">="&(ROW(K2)-ROW(K$2))*20,I$2:I$778,"<"&(ROW(K2)-ROW(K$2)+1)*20)

Markmzz

 

[Peter_SSs]

I small modification in the Peter's formula:

Although that will produce the results without the helper cells, I would argue it is less useful..

a) If this was dragged down more than just a few rows it would become increasingly unclear what time period a particular result belonged to.

b) If it is subsequently decided to change the time period from 20 seconds to some other number, I think it easier to just change my cell M2 than to alter the formula and re-copy it down. Again the visibility of the start value of each time-period and its duration would be useful for the sheet viewer/user in this circumstance.

Anyway, that's just my view and you have certainly offered the OP an alternative that they may choose to use.



(BTW, if avoiding the helper cells, using ROWS instead of ROW would allow a slight shortening and a reduction in the number of function calls)

=AVERAGEIFS(J$2:J$778,I$2:I$778,">="&(ROWS(K$2:K2)-1)*20,I$2:I$778,"<"&ROWS(K$2:K2)*20)

 

[markmzz]

Although that will produce the results without the helper cells, I would argue it is less useful..

a) If this was dragged down more than just a few rows it would become increasingly unclear what time period a particular result belonged to.

b) If it is subsequently decided to change the time period from 20 seconds to some other number, I think it easier to just change my cell M2 than to alter the formula and re-copy it down. Again the visibility of the start value of each time-period and its duration would be useful for the sheet viewer/user in this circumstance.

Anyway, that's just my view and you have certainly offered the OP an alternative that they may choose to use.



(BTW, if avoiding the helper cells, using ROWS instead of ROW would allow a slight shortening and a reduction in the number of function calls)

=AVERAGEIFS(J$2:J$778,I$2:I$778,">="&(ROWS(K$2:K2)-1)*20,I$2:I$778,"<"&ROWS(K$2:K2)*20)

I only offered another way (not the most smart), but another way.

Anyway, I think that the important thing is to solve the user problem.

Markmzz