Conditional statements

wc115

New Member
Hi,

I want to find the standard deviation on a certain set of data with conditional statements.
For example I want to find the standard deviation of Sales only if the Rank = A and Month = 1.

 Rank SALES MONTHS A 1 1.00 A 10 1.00 A 2 1.00 A 10 1.00 A 1 1.00 B 3 1.00 B 3 2.00 B 3 1.00 B 3 2.00 B 3 1.00 B 3 2.00 B 3 1.00

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

This is what I came up with:
=STDEV(IF(AND(A2:A6=H2,C2=1),B2:B6))

It works if I only take the first 5, as soon as I take the rest my result changes but it should not because of the conditions.

Any help would be appreciated.

Thanks!

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

helpexce

Board Regular
Try this one.

=STDEV(IF(\$A\$2:\$A\$13="A",IF(\$C\$2:\$C\$13=1,\$B\$2:\$B\$13)))

Confirm with Ctrl+Shift+Enter and not just Enter.

AhoyNC

Well-known Member
Try.
This is an array function and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHI
1RankSALESMONTHSStDev.
2A114.764452A
3A101
4A21
5A101
6A11
7B31
8B32
9B31
10B32
11B31
12B32
13B31
14
Sheet

Replies
3
Views
124
Replies
3
Views
222
Replies
3
Views
228
Replies
5
Views
224
Replies
6
Views
154

1,195,582
Messages
6,010,579
Members
441,557
Latest member
Jbest23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back