Combining VLOOKUP, IF OR, and STDEV

Question

In Excel I have two columns. One has a player's name and the other has points scored. Like this:

Player  Points
Foo     10
Bar     11
Foo     23
Test    9
Joe     1
Foo     2

What I'm trying to do is get the standard deviation of the points for a combined list of players.

For example, if I had this list:

Foo
Bar

I would want the standard devation of 10, 11, 23, and 2, since those are the values that match those two players.

I have tried this formula:

=STDEV(IF(OR(A:A="Foo",A:A="Bar"),B:B,""))

but I get a different answer than if I were to use the STDEV formula on the individual numbers that it should be using.

Anyone know if this is possible? Thanks!

Answer 1

You can use following array formula (Ctrl+Shift+Enter):

=STDEV(IF((A:A="Foo")+(A:A="Bar")>0;B:B))

Answer 2

Your formula doesn't work, because OR returns true if any of its arguments are true. Since there's at least one Foo in your list, OR always returns true, so IF returns all values in the B column.

You can see this by entering: =OR(A:A="Foo"). It will show TRUE.

An alternative is to do a substring search, such as FIND("|"&A:A&"|","|Foo|Bar|").

I'm using pipes (|) as delimiters.

This will return #VALUE! when not found, but you can use ISNUMBER to return false in those cases.

Your formula then becomes =STDEV(IF(ISNUMBER(FIND("|"&A:A&"|","|Foo|Bar|")),B:B)).

Enter as an array formula: Ctrl + Shift + Enter.

Array formulas can be really slow when working on entire columns. So you may want to restrict the data to the actual range – A2:A7 and B2:B7 in this example:

Answer 3

@Rick is right, but a simpler way to "correct" ORs behaviour is to use the simple + in its place (needs to be array formula, so entered with Ctrl+Shift+Enter)-->

=STDEV(IF((A:A="Foo")+(A:A="Bar"),B:B,""))