Averaging Data Every 20 Seconds

G

Geijer85

New Member
Joined
Jun 16, 2013
Messages
6
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Code: 
       ---A---- ---B---- --C--
   1   Time [s] Dilation  Avg 
   2      0.000    1.016      
   3      2.370    0.678      
   4      4.438    0.339      
   5      4.875    1.356      
   6      6.906    1.016      
   7     12.156    1.355 0.736
   8     13.344    0.678 0.742
   9     14.016   -1.017 0.742
  10     14.422    1.102 0.742
  11     17.563   -0.399 0.718
  12     18.063    1.356 0.718
  13     19.279    1.017 0.718
  14     20.828    1.356 0.777
  15     24.563    1.011 0.789
  16     29.563    1.069 0.875
  17     30.375    1.016 0.905
  18     31.172    0.000 0.840
  19     31.938    0.677 0.857
  20     36.441    0.998 0.938
  21     39.980    1.110 0.921
  22     41.353    0.976 1.059
  23     43.547    1.359 1.135
  24     44.112    1.234 1.135

The formula in C2 and copied down is

Code: 
=IF(A2<10, "", 
   SUMIFS($B$2:$B$24, $A$2:$A$24, ">=" & A2-10, $A$2:$A$24, "<=" & A2+10) / 
   COUNTIFS(          $A$2:$A$24, ">=" & A2-10, $A$2:$A$24, "<=" & A2+10))
 
Upvote 0
Thank you, shg. However, that formula does not seem to be producing the information I am looking for. I would like column C to only produce the average dilation of every 20 seconds. So C1 would be the average dilation of seconds 0-19.99, C2 would be the average dilation of seconds 20-39.99, etc. Is there a way to produce such results?

Again, thank you.
 
Upvote 0
Welcome to the MrExcel board!

Does this do what you want?
Both formulas copied down. Adjust the ranges in the column E formula to suit the size of your data.

Excel Workbook
ABCDE
1Time (sec)Percent DilationStart Time (sec)Duration (sec)Average
201.0160200.708083333
32.370.678200.904625
44.4380.339401.189666667
54.8751.356
66.9061.016
712.1561.355
813.3440.678
914.016-1.017
1014.4221.102
1117.563-0.399
1218.0631.356
1319.2791.017
1420.8281.356
1524.5631.011
1629.5631.069
1730.3751.016
1831.1720
1931.9380.677
2036.4410.998
2139.981.11
2241.3530.976
2343.5471.359
2444.1121.234
25
Averages
 
Upvote 0
Hello and thank you, Peter!

I am getting the #DIV/0! result in my cells. Here is the formula I utilized:

=AVERAGEIFS(J$2:J$778,I$2:I$778,">="&L2,I$2:I$778,"<"&L2+M$2)

I'm not sure where I'm going wrong. Any ideas?

Thank you!
 
Upvote 0
Geijer85 said:
Hello and thank you, Peter!

I am getting the #DIV/0! result in my cells. Here is the formula I utilized:

=AVERAGEIFS(J$2:J$778,I$2:I$778,">="&L2,I$2:I$778,"<"&L2+M$2)

I'm not sure where I'm going wrong. Any ideas?

Thank you!
Click to expand...
I would expect to get that error if there are no Dilation figures for a time range, and we can deal with that if required.
However, if you are getting the error in all cells then possibly one or both your columns are Text, not Numbers.

In vacant cells, what do these formulas return?
=ISNUMBER(I2)
=ISNUMBER(J2)
 
Upvote 0
Peter_SSs said:
I would expect to get that error if there are no Dilation figures for a time range, and we can deal with that if required.
However, if you are getting the error in all cells then possibly one or both your columns are Text, not Numbers.

In vacant cells, what do these formulas return?
=ISNUMBER(I2)
=ISNUMBER(J2)
Click to expand...


All of the cells containing the formula you provided return the same error message. I formatted all cells to be numbers, and when entering =ISNUMBER(J2) or =ISNUMBER(I2) for missing data, the value TRUE was returned.
 
Upvote 0
Geijer85 said:
All of the cells containing the formula you provided return the same error message. I formatted all cells to be numbers, and when entering =ISNUMBER(J2) or =ISNUMBER(I2) for missing data, the value TRUE was returned.
Click to expand...
Then I'm not sure what the issue is. Here is my sheet again, rearranged to use the columns you appear to be using. Formulas are not returning errors.

Excel Workbook
IJKLMN
1Time (sec)Percent DilationStart Time (sec)Duration (sec)Average
201.0160200.708083333
32.370.678200.904625
44.4380.339401.189666667
54.8751.356
66.9061.016
712.1561.355
813.3440.678
914.016-1.017
1014.4221.102
1117.563-0.399
1218.0631.356
1319.2791.017
1420.8281.356
1524.5631.011
1629.5631.069
1730.3751.016
1831.1720
1931.9380.677
2036.4410.998
2139.981.11
2241.3530.976
2343.5471.359
2444.1121.234
25
Averages



Can you confirm the layout is correct?

Try starting a new worksheet and manually type in a few rows of data and the formulas. What happens in that sheet?
 
Upvote 0
Peter_SSs said:
Then I'm not sure what the issue is. Here is my sheet again, rearranged to use the columns you appear to be using. Formulas are not returning errors.

Averages

*IJKLMN
*
*
**
**
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
****
******

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:80px;"><col style="width:107px;"><col style="width:26px;"><col style="width:116px;"><col style="width:97px;"><col style="width:97px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]Time (sec)[/TD]
[TD="align: right"]Percent Dilation[/TD]

[TD="align: right"]Start Time (sec)[/TD]
[TD="align: right"]Duration (sec)[/TD]
[TD="align: right"]Average[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.708083333[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2.37[/TD]
[TD="align: right"]0.678[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]0.904625[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4.438[/TD]
[TD="align: right"]0.339[/TD]

[TD="align: right"]40[/TD]

[TD="align: right"]1.189666667[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4.875[/TD]
[TD="align: right"]1.356[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]6.906[/TD]
[TD="align: right"]1.016[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]12.156[/TD]
[TD="align: right"]1.355[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]13.344[/TD]
[TD="align: right"]0.678[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]14.016[/TD]
[TD="align: right"]-1.017[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]14.422[/TD]
[TD="align: right"]1.102[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]17.563[/TD]
[TD="align: right"]-0.399[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]18.063[/TD]
[TD="align: right"]1.356[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]19.279[/TD]
[TD="align: right"]1.017[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]20.828[/TD]
[TD="align: right"]1.356[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]24.563[/TD]
[TD="align: right"]1.011[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]29.563[/TD]
[TD="align: right"]1.069[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]30.375[/TD]
[TD="align: right"]1.016[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]31.172[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]31.938[/TD]
[TD="align: right"]0.677[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]36.441[/TD]
[TD="align: right"]0.998[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]39.98[/TD]
[TD="align: right"]1.11[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]41.353[/TD]
[TD="align: right"]0.976[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]43.547[/TD]
[TD="align: right"]1.359[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]44.112[/TD]
[TD="align: right"]1.234[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
N2=AVERAGEIFS(J$2:J$778,I$2:I$778,">="&L2,I$2:I$778,"<"&L2+M$2)
L3=L2+M$2

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Can you confirm the layout is correct?

Try starting a new worksheet and manually type in a few rows of data and the formulas. What happens in that sheet?
Click to expand...


Everything works when I retype the data. Could it be that the seconds and the diameter are calculated with formulas rather than being typed directly?
 
Upvote 0
I tried copying those columns to a new sheet and pasting the values only and everything works to perfection! Thank you, Peter!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top