HELP with STDEVPA and Returning Multiple Individual Values

Hey guys,

I'm having trouble with writing an equation that would help me perform a STDEVPA function on sales for each individual item sold in the past year. The Sales Record raw data sheet has thousands of records, with each individual item having multiple sales transactions. My goal is to reference the item number and bring back multiple individual values for each of the item number to derive their standard deviations. I've tried using the Index, Match, Small, Row functions but am not experienced enough with those to write an equation that would help me.

I'm really hoping you guys can help me with this. Sorry, I can't attach my workbook for security reasons.

Please find an over-simplified mock Sales Sheet attached that should help make it easier to understand.

Mock Sales Sheet - Excel Help.xlsx

Thanks in advance!!

If you can't post a mockup, it's going to be difficult for anyone to help.

May be you could use basic equation for standard deviation - see similar thread: http://www.excelforum.com/excel-gene...ml#post3624272

Via formula and pivot table:

	C	D	E	F	G	H	I	J	K
2	Item	Desc	Qty	Unit Cost			Item	SD of Qty
3	A12345	XA	2	$ 5.00			A12345	1.090	J3: {=STDEVP(IF($C$3:$C$21=I3, $E$3:$E$21))}
4	A12345	XA	1	$ 5.00			B12345	2.055
5	A12345	XA	2	$ 5.00			C12345	0.471
6	A12345	XA	4	$ 5.00			D12345	2.000
7	B12345	XB	3	$ 10.00			E12345	2.586
8	B12345	XB	8	$ 10.00
9	B12345	XB	8	$ 10.00
10	B12345	XB	5	$ 10.00			Row Labels	StdDevp of Qty
11	B12345	XB	3	$ 10.00			A12345	1.090
12	B12345	XB	5	$ 10.00			B12345	2.055
13	C12345	XC	3	$ 2.00			C12345	0.471
14	C12345	XC	4	$ 2.00			D12345	2.000
15	C12345	XC	4	$ 2.00			E12345	2.586
16	D12345	XD	4	$ 15.00			Grand Total	2.253
17	D12345	XD	8	$ 15.00
18	E12345	XE	6	$ 20.00
19	E12345	XE	1	$ 20.00
20	E12345	XE	8	$ 20.00
21	E12345	XE	4	$ 20.00

I'm missing the relevance of cost in this calculation.

Try this in Cell J3...


=STDEVPA(IF($C$3:$C$21=I3,$E$3:$E$21))
**This is an Array formula.. need to press CTRL+SHIFT+ENTER when entering it into cells

**EDIT: forgot the IF... same as yours shg

That evaluates all the rows where there is no match as zero values.

You're welcome.