# Standard deviation based on multiple criteria in a separate column

1. ## Standard deviation based on multiple criteria in a separate column

So, I'm trying to do some varying calculations and I've gotten my average to work fine, but I need to do standard deviation based on the same criteria.

Column A is the one I need to do the deviation on
Column B is the criteria column, if it is X or Y, then I need to do the deviation based on that for Column A
Then, I need to do the deviation on the remaining that isn't X or Y (This is simple enough to do if I can just get the first working)

My average is calculated as such: =AVERAGEIFS(A:A, B:B, {"X","Y"})
It doesn't appear that I can do STDEVP based on the same multiple criteria, like I was hoping...that or it's late, and I've been staring at spreadsheets for 12+ hours already today

3. ## Re: Standard deviation based on multiple criteria in a separate column

if criteria is indeed X and Y or similar sequence of letters from ascii code try such (a bit long, but shall work) array formula:
``Please Login or Register  to view this content.``
in one cell and copy it right.
te "magic 87" is just
``Please Login or Register  to view this content.``
if you want to copy it down (not right) substirute COLUMN with ROW.

4. ## Re: Standard deviation based on multiple criteria in a separate column

Originally Posted by sstyre
=AVERAGEIFS(A:A, B:B, {"X","Y"})
I don't think that works - that formula returns an array of two values, one for x and one for y, but if you place in a single cell you will only see the average for the first value, x in this case

Try an array formula like this:

=AVERAGE(IF(A1:A10={"x","y"},B1:B10))

confirmed with CTRL+SHIFT+ENTER

adjust ranges as require, preferably not whole columns

You can use that exact same syntax for other functions, so for standard deviation just replace AVERAGE with STDEV

5. ## Re: Standard deviation based on multiple criteria in a separate column

It ( =AVERAGEIFS(A:A, B:B, {"X","Y"}) ) shall work and works - see attachment.

You just need to select two cells (not sure vertical or horizontal - depends on what separators are in english version of excel for separating columns and what for separating rows) write the formula and CSE commit. You will have one array formula for both cells.

6. ## Re: Standard deviation based on multiple criteria in a separate column

Hello Kaper,

Yes, I know you can put that formula in a two cell range.....but I'm assuming that sstyre wants a single result, the average of column A where column B is "x" or "y". You can't produce that single result with AVERAGEIFS so I'm suggesting that an array formula will do that....and the same syntax can give you standard deviation for the same criteria

7. ## Re: Standard deviation based on multiple criteria in a separate column

Hi,
Honestly - I've been sure that you know, but expected, that you expect sstyre does not ... ;-)
OK it starts to be twisted too much.
Let's wait until Bow starts to flow - it's almost 6:00 there.

8. ## Re: Standard deviation based on multiple criteria in a separate column

Let's hope sstyre is not getting the Bow water out of his basement. We need his sample file.

9. ## Re: Standard deviation based on multiple criteria in a separate column

Thanks all for the input so far. I hadn't included my file initially as it contains some very sensitive client information, and just didn't have the time or energy last night to pull it out to make a sample otherwise. However, not the case now, and file is attached, with some attempts at what's been provided here.

@daddylonglegs, I want the average of column A if column B is X or Y as a single result, you are correct. The AVERAGEIFS was indeed returning the incorrect value, I hadn't done the simple calc last night to confirm, however I get a #DIV/0 error based on what you provided (Note that I changed your column's around, as I had them opposite to what your formula stated). So, then I run into the STDEVP causing the same #DIV/0.

Time for a coffee before I settle in for a long day of spreadsheets again!

10. ## Re: Standard deviation based on multiple criteria in a separate column

in EUC/RECYCLE (H11) put
``Please Login or Register  to view this content.``
and then Alt-Shift-Enter to get "{"

11. ## Re: Standard deviation based on multiple criteria in a separate column

in EUC/RECYCLE (H11) put
``Please Login or Register  to view this content.``
and then Alt-Shift-Enter to get "{"
I had been using ALT+SHIFT+ENTER, but it wasn't doing me any good, however saved, closed and reopened, and now it does, for H11...maybe it was the coffee This also fixed E11 for the average.

Now, I just need to fix my OTHER calc's in E12 and H12. I found the obvious error, as I had it setup to be =AVERAGE(IF(B2:B291={"<>EUC","<>RECYCLE"},A2:A291)), however, that should actually be =AVERAGE(IF(B2:B291<>{"EUC","RECYCLE"},A2:A291)). Now, the only problem is, it's returning a different value than what it should...not by a lot, but by a bit. E12 should average out to 13.92 but this formula averages it out to 13.78. H12 should be 6.25, but it's coming out with 6.65...the overall averages, versus the average of all except for EUC and RECYCLE.

I can fix it by simply changing the formula to be =AVERAGE(IF(B2:B291={"DATA","MAINT","NET","SVC","SW"},A2:A291)), however, if there's changes down the road that add something to that column, then I need to modify it too. Time to play with the <> to get it working.

Thanks!

12. ## Re: Standard deviation based on multiple criteria in a separate column

Sorry, it was Ctrl-Shift-Enter
for H12 I got: 6.46

13. ## Re: Standard deviation based on multiple criteria in a separate column

When you are using <> for multiple items your syntax needs to change - for excluding 2 items you can use this method:

=AVERAGE(IF(B2:B291<>"EUC",IF(B2:B291<>"RECYCLE",A2:A291)))

or this also works and is better for a longer list of exclusions

=AVERAGE(IF(ISNA(MATCH(B2:B291,{"EUC","RECYCLE"},0)),A2:A291))

either way confirm with CTRL+SHIFT+ENTER

Those methods can both be used for STDEVP too but for average only you can also use AVERAGEIFS when excluding multiple values

=AVERAGEIFS(A2:A291,B2:B291,"<>EUC",B2:B291,"<>RECYCLE")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1