Calculate standard deviation of same text values between cells in two columns

[smide]

Hello.


In columns B and C (B2:C600) I have a product list (about 25 different products) and in columns E and F their prices.
When the product is in Column B his price is in Column E (same row) and when the product is in Column C his price is in Column F.


In column H (from cell H2 and downwards) I have list off ALL potential products.


I need a formula to find standard deviation (STDEV) of all prices for each product and put those results in Column I.


example.

	A	B	C	D	E	F	G	H	I
1					Price	Price		Product list
2		apple	sugar		12	10		apple	2.5
3		orange	milk		8	21		sugar	...
4		flour	cookies		16	3		orange	...
5		cookies	apple		18	9		milk	...
6		cookies	milk		1	22		flour	...
7		apple	orange		15	11		cookies	...
8		sugar	flour		4	9
9		flour	apple		18	13
10		...	...		...	...

<tbody>
</tbody>

cell I2 is STDEV for all apple prices: = stdev(E2,F5,E7,F13)=stdev(12,9,15,13) = 2.5

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G
1			Price	Price		Product list
2	apple	sugar	12	10		apple	2.5
3	orange	milk	8	21		sugar	4.242641
4	flour	cookies	16	3		orange	2.12132
5	cookies	apple	18	9		milk	0.707107
6	cookies	milk	1	22		flour	4.725816
7	apple	orange	15	11		cookies	9.291573
8	sugar	flour	4	9
9	flour	apple	18	13
Sheet14

Cell Formulas
Range	Formula
G2	{=STDEV(IF($A$2:$B$9=F2,OFFSET($A$2:$B$9,0,2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

In I2 control+shift+enter, not just enter, and copy down:

=STDEV(IF($B$2:$C$9=H2,$E$2:$F$9))