Calculating average of triplicates in a single column of data

[nerdyguy29]

Hi,
I need to calculate the averages from a column of triplicate data, and am struggling to find and correctly use a formula to do this.
Here's what the data looks like:
Column A Column B Column C
ID number Trial number Measurement
1 1 100
1 2 50
1 3 50
2 1 200
2 2 150
2 3 148
3 1 125
3 2 99
3 3 110


Any help would be greatly appreciated. Thanks!!

 

[jtakw]

Hi,

Use AVERAGEIF:

Excel 2010
	A	B	C	D	E	F
1	ID Number	Trial Number	Measurement		ID Number	Avg. Measurement
2	1	1	100		1	66.66666667
3	1	2	50		2	166
4	1	3	50		3	111.3333333
5	2	1	200
6	2	2	150
7	2	3	148
8	3	1	125
9	3	2	99
10	3	3	110
Sheet1

Cell Formulas
Range	Formula
F2	=AVERAGEIF(A$2:A$10,E2,C$2:C$10)

F2 formula copied down.

 

[nerdyguy29]

Thank you so much! Worked perfectly!! Keep up the great work!!

Hi,

Use AVERAGEIF:

Excel 2010

	A	B	C	D	E	F
ID Number	Trial Number	Measurement	ID Number	Avg. Measurement

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]66.66666667[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]166[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]111.3333333[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=AVERAGEIF(A$2:A$10,E2,C$2:C$10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



F2 formula copied down.

 

[jtakw]

You're welcome, welcome to the forum.