Using STDEV.S with VLOOKUP, or alternate solutions

1
votes

In Excel, I have two columns. One has a category name and the other has a number against the category like this:

Category List

What I'm trying to do is get the standard deviation of the points for each individual category, in a 3rd column.

For example, if I had the above data set, the Category STDEV.S column would show: Category List 2

I would want the standard deviation of 10, 7 and 7 to show against each category that shows as "Fruit", and the standard deviation of 11, 5 and 7 to show against each category that shows as "Starch".

I tried referring to this thread as a reference, but could not get the formula to work: Combining VLOOKUP, IF OR, and STDEV

1
excelexcel-formulastdev
what was the formula you tried? it could be as simple as forgetting the last step of using Ctrl-Shift-Enter instead of Enter when exiting edit mode. – Scott Craner

1 Answers

1
votes

use this array formula:

=STDEV.S(IF($G$2:$G$13=G2,$H$2:$H$13))

Confirm the formula with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here