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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Price [/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product list[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]2.5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]milk[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]flour[/TD]
[TD="align: center"]cookies[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]cookies[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]milk[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]cookies[/TD]
[TD="align: center"]milk[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"]flour[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]cookies[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"]flour[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]flour[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"][/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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))