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.

Time (sec)	Percent Dilation
0	1.016
2.37	0.678
4.438	0.339
4.875	1.356
6.906	1.016
12.156	1.355
13.344	0.678
14.016	-1.017
14.422	1.102
17.563	-0.399
18.063	1.356
19.279	1.017
20.828	1.356
24.563	1.011
29.563	1.069
30.375	1.016
31.172	0
31.938	0.677
36.441	0.998
39.98	1.110
41.353	0.976
43.547	1.359
44.112	1.234

<tbody>
</tbody>

Please, please help.

Thank you.

 

[shg]

       ---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

=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))

 

[Geijer85]

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.

 

[Peter_SSs]

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
	A	B	C	D	E
1	Time (sec)	Percent Dilation	Start Time (sec)	Duration (sec)	Average
2	0	1.016	0	20	0.708083333
3	2.37	0.678	20		0.904625
4	4.438	0.339	40		1.189666667
5	4.875	1.356
6	6.906	1.016
7	12.156	1.355
8	13.344	0.678
9	14.016	-1.017
10	14.422	1.102
11	17.563	-0.399
12	18.063	1.356
13	19.279	1.017
14	20.828	1.356
15	24.563	1.011
16	29.563	1.069
17	30.375	1.016
18	31.172	0
19	31.938	0.677
20	36.441	0.998
21	39.98	1.11
22	41.353	0.976
23	43.547	1.359
24	44.112	1.234
25
Averages

 

[Geijer85]

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!

 

[Peter_SSs]

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!

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)

 

[Geijer85]

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)

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.

 

[Peter_SSs]

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.

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
	I	J	K	L	M	N
1	Time (sec)	Percent Dilation		Start Time (sec)	Duration (sec)	Average
2	0	1.016		0	20	0.708083333
3	2.37	0.678		20		0.904625
4	4.438	0.339		40		1.189666667
5	4.875	1.356
6	6.906	1.016
7	12.156	1.355
8	13.344	0.678
9	14.016	-1.017
10	14.422	1.102
11	17.563	-0.399
12	18.063	1.356
13	19.279	1.017
14	20.828	1.356
15	24.563	1.011
16	29.563	1.069
17	30.375	1.016
18	31.172	0
19	31.938	0.677
20	36.441	0.998
21	39.98	1.11
22	41.353	0.976
23	43.547	1.359
24	44.112	1.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?

 

[Geijer85]

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

*	I	J	K	L	M	N
1	Time (sec)	Percent Dilation	*	Start Time (sec)	Duration (sec)	Average
2	0	1.016	*	0	20	0.708083333
3	2.37	0.678	*	20	*	0.904625
4	4.438	0.339	*	40	*	1.189666667
5	4.875	1.356	*	*	*	*
6	6.906	1.016	*	*	*	*
7	12.156	1.355	*	*	*	*
8	13.344	0.678	*	*	*	*
9	14.016	-1.017	*	*	*	*
10	14.422	1.102	*	*	*	*
11	17.563	-0.399	*	*	*	*
12	18.063	1.356	*	*	*	*
13	19.279	1.017	*	*	*	*
14	20.828	1.356	*	*	*	*
15	24.563	1.011	*	*	*	*
16	29.563	1.069	*	*	*	*
17	30.375	1.016	*	*	*	*
18	31.172	0	*	*	*	*
19	31.938	0.677	*	*	*	*
20	36.441	0.998	*	*	*	*
21	39.98	1.11	*	*	*	*
22	41.353	0.976	*	*	*	*
23	43.547	1.359	*	*	*	*
24	44.112	1.234	*	*	*	*
25	*	*	*	*	*	*

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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?

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?

 

[Geijer85]

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