STDEVA with OR arguments

[jbrecevic]

Morning All,

Long time lurker, first time poster. Firstly thanks for all the help over the years, really helped me out. Has anyone used or arguments successfully (Non-VBA route due to audience restrictions)?

I have 2 "buckets"

A 4
B 6
C 7

D 1
E 3
F 9

I'm trying to have a formulae that finds the STDEVA of bucket 1 and the STDEVA of bucket 2 from a dataset

=STDEVA(IF(OR(Data!A:A=Output!B9,Data!A:A=Output!B10,Data!A:A=Output!B11),Data!B:B)) - In array terms

So I have a dataset filled with A B C D E F letters and a value and would like an easy way to find the std dev of group 1 (all the ABCs) and the stdev of group 2 (DEFs)

Any pointers much appreciated!

Thanks

 

[FormR]

Hi, welcome to posting

Something like this maybe..:

Excel 2013/2016
	A	B	C	D	E
1	A	4		A	1.527525232
2	B	6		B
3	C	7		C
4	D	1
5	F	3
6	F	9
Sheet1

Cell Formulas
Range	Formula
E1	{=STDEVA(IF(ISNUMBER(MATCH(A1:A6,D1:D3,0)),B1:B6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[barry houdini]

OR always returns a single result rather than the array you need here, try using + like this:

=STDEVA(IF((Data!A:A=Output!B9)+(Data!A:A=Output!B10)+(Data!A:A=Output!B11),Data!B:B))

or you can use MATCH like this

=STDEVA(IF(ISNUMBER(MATCH(Data!A:A,Output!B9:B11,0)),Data!B:B))​

​

 

[jbrecevic]

Excellent, thank you both so much