Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Basic Chart

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

Chart with Error Bars

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.