# Conditional standard deviation and standard errors in Excel

I have data I would like to chart in an Excel 2013 spreadsheet. Here's a fragment of the data

```-------------------------------------------
| intervalMode  | keyboardEventsPerSecond |
-------------------------------------------
| orienting     | 0                       |
| orienting     | 0                       |
| orienting     | 0.171115674             |
| orienting     | 0                       |
| orienting     | 0                       |
| opportunistic | 0                       |
| opportunistic | 0.016913605             |
| opportunistic | 0                       |
| opportunistic | 0                       |
-------------------------------------------
```

When I chart the data I'd like to put the average for each mode. This is easily achieved with a table containing the formula

`=AVERAGEIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)`

which uses the first and second column of the fragment shown as its first and third argument. Using that formula I get this table:

```--------------------------------------------------
| intervalMode  | average keyboardEventsPerSecond |
--------------------------------------------------
| lean-back     | 0.009044655                     |
| opportunistic | 0.01058782                      |
| orienting     | 0.036665215                     |
| purposeful    | 0.03851359                      |
| respite       | 0.120037091                     |
--------------------------------------------------
```

That's great. If I chart that table I get this

I'd like to add error bars, which I can do in the Excel chart tools. However I end up with this

The error bars for the first two bars extend below the axis, and they should not.

To fix this I was going to switch to custom error bars and add two columns to my table for the positive and the negative error bars. Hence I expect to use a formula something like

`=STERRIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)`

But no formulas seem to be built in for the standard error. So I could roll it myself using something like

`=STDEVIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)/SQRT(COUNTIF(AllDataFromSQL!A2:A14057,Graphs!A3))`

But `STDEVIF` does not seem to exist. Of course I can add extra columns and calculate that by hand, but is there a better way to calculate the standard error in the same way I have used `AVERAGEIF` to simply calculate a conditional average?

-

## 1 Answer

You can calculate a conditional standard deviation by using an "array formula" - the syntax would be like this

`=STDEV(IF(AllDataFromSQL!A\$2:A\$14057=Graphs!A3,AllDataFromSQL!B\$2:B\$14057))`

That's an array formula and as such you need to confirm with CTRL+SHIFT+ENTER. To do that select cell with formula, press F2 to select formula and then hold down CTRL and SHIFT while pressing ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar.

You need to do that for the first formula....then you can copy/fill down

-
Excellent - thanks Barry. That works, and interestingly the resulting values for the standard error match those I'm getting from SQL's `STDEV(keyboardEventsPerSecond)/SQRT(COUNT(*))` unlike the standard error bars that Excel was adding automatically. And I'd forgotten all about CTRL+SHIFT+ENTER! – dumbledad Mar 5 '13 at 10:53