how to bunch up multiple seconds to find an average/min/max per minute?

[frysie]

Hi,

I have heart rate data that gets calculated every second or so. What i would like excel to do is only show the min, max, avg for each minute on a row. I am manually doing this so there must be an easier way.


ie.

11:41 155 180 175
11:42 165.... etc.

https://drive.google.com/open?id=0B73a6NvDa84XM0NydjJSZ2Z3ZUE&authuser=0

 

[asif_raza]

Simple formulas for avg, min and max. Just
=AVERAGE(C2:L2)
=MIN(C2:L2)
=MAX(C2:L2)

Here is your file edited: https://drive.google.com/file/d/0B2PdG6oqRTw4a1ZvSGs0MGNaNms/view?usp=sharing

 

[frysie]

sorry should of been more clear.

I am only interested in the HR (bpm) column (column 2) with respects to the avg/min/max

i want to sum up all the values within each minute and output the min/max/avg for each minute

 

[asif_raza]

1. HR (bpm) is in Column C. since last row is 2361

=AVERAGE(C2:C2361)
=MIN(C2:C2361)
=MAX(C2:C2361)

2. min/max/avg for each minute
=AVERAGE(C2:L2)
=MIN(C2:L2)
=MAX(C2:L2)

 

[frysie]

i need the min/max & avg of the sum of each hr (bpm) (Column C) ie.

time min avg max
11:01 130 160 180
11:02 135 165 185
11:03 .........................

 

[RandyMorris]

Table 1: Setup up | Table 2: Answer | Table 3: Formulae

I downloaded your spreadsheet and Time is in A1. I copy and pasted column A into column Q, and then I removed duplicate values via the data menu on the excel ribbon. The minutes I had left were from 11:19 to 11:58 (one of each). 11:19 is in Q2, and so on.

I used AverageIF to get the averages for each minute, but I had to use array formulas (CTRL+SHIFT+ENTER after you type the formula) to get the min and max. Please see table 3 for those formulae.

In the formulae: Column A = All 2360 time measurements | Column Q = where I removed duplicate values | Column B = HR (bpm) | The squiggly lines are the array formula

	A	B
1	Time	HR (bpm)
2	11:19	143
62	11:20	162

<tbody>
</tbody>

Time	Avg	Min	Max
11:19	144.12	140.00	165.00
11:20	158.53	151.00	166.00

<tbody>
</tbody>

Avg	Min	Max
AVERAGEIF(A:A,Q2,B:B)	{=MIN(IF(A:A=Q2,B:B))}	{=MAX(IF(A:A=Q2,B:B))}
AVERAGEIF(A:A,Q3,B:B)	{=MIN(IF(A:A=Q3,B:B))}	{=MAX(IF(A:A=Q3,B:B))}

<tbody>
</tbody>

 

[frysie]

@randymorris Thanks!! That is perfect you're a champ!

 

[RandyMorris]

Thanks for letting us know it worked and glad we could help. Cheers.