Average Standard Deviation in Pivot Table for multiple entries

[Shivi0189]

I have data in the below format:

BOAT NO	LOT NO	D1	D2	D3	D4	D5	STATUS
F4/337	5973	10.6	10.59	10.59	10.59	10.59	ACC
F4/338	5973	10.6	10.58	10.59	10.6	10.6	ACC
F4/339	5915	10.61	10.62	10.61	10.61	10.62	ACC
F4/340	5986	10.59	10.58	10.59	10.58	10.58	ACC
F4/341	5915	10.47	10.49	10.48	10.48	10.49	ACC
F4/342	5915	10.61	10.62	10.64	10.48	10.62	ACC

<colgroup><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>

I have made a pivot table where I have sort data as per lot no. I want to take average & stdev of entries in D1,...D5 for each lot no. spread in 5 columns, How to do that?

 

[sandy666]

like this?

	Attribute	Values
	D1		D2		D3		D4		D5
LOT NO	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value
5915​	10.56333333​	0.080829038​	10.57666667​	0.075055535​	10.57666667​	0.085049005​	10.52333333​	0.075055535​	10.57666667​	0.075055535​
5973​	10.6​	0​	10.585​	0.007071068​	10.59​	0​	10.595​	0.007071068​	10.595​	0.007071068​
5986​	10.59​	#DIV/0!​	10.58​	#DIV/0!​	10.59​	#DIV/0!​	10.58​	#DIV/0!​	10.58​	#DIV/0!​

 

[Shivi0189]

like this?

	Attribute	Values
	D1		D2		D3		D4		D5
LOT NO	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value	Average of Value	StdDev of Value
5915​	10.56333333​	0.080829038​	10.57666667​	0.075055535​	10.57666667​	0.085049005​	10.52333333​	0.075055535​	10.57666667​	0.075055535​
5973​	10.6​	0​	10.585​	0.007071068​	10.59​	0​	10.595​	0.007071068​	10.595​	0.007071068​
5986​	10.59​	#DIV/0!​	10.58​	#DIV/0!​	10.59​	#DIV/0!​	10.58​	#DIV/0!​	10.58​	#DIV/0!​

<tbody>
</tbody>

No. I want only one average & stdev value for all 5 attributes(D1,D2,D3,D4,D5) against every lot no.

 

[sandy666]

like this?

LOT NO	Average of Value	StdDev of Value
5915​	10.56333333​	0.069556418​
5973​	10.593​	0.006749486​
5986​	10.584​	0.005477226​

 

[Shivi0189]

Yes

like this?

LOT NO	Average of Value	StdDev of Value
5915​	10.56333333​	0.069556418​
5973​	10.593​	0.006749486​
5986​	10.584​	0.005477226​

<tbody>
</tbody>

 

[sandy666]

so use PowerQuery (Get&Transform)...
load your source table into PQ editor, select D columns, from the ribbon - Transform - Unpivot columns select Unpivot Only Selected Columns
then Close&Load as Connection
---
from Insert - PivotTable choose: use an external data source then Choose Connection and Query - Table1 (Open - OK)
put Lot No into ROWS Area, Value into the VALUES Area twice, one for Average, second for StdDev