STDEV with multiple conditions

Hi all,

I have a population of test data within which I want to analyze the distribution of the individual subsets.

Right now I have a formula written like this:

=STDEV.S(IF(AND(DataSheet!$S:$S=Statistics!A3,DataSheet!$AT:$AT=Statistics!B3),DataSheet!$BC:$BC))

Here, I have two conditions specified with AND:

DataSheet!$S:$S=Statistics!A3,DataSheet!$AT:$AT=Statistics!B3

For the IF conditions

IF(AND(),DataSheet!$BC:$BC)

This should result in an array input for the formula STDEV.S... but I am getting a result of 0.

I have done this same calculation with Matlab for verification and (obviously) the σ is not zero... I am not so great at working with Excel functions so please advise

Hello and welcome to the forum.

With arrays, AND is written by multiplying two arrays together (and add for OR).

Since I don't have a sample sheet in front of me, all that I can do is correct the syntax for your formula. That is:

=STDEV.S(IF((DataSheet!$S:$S=Statistics!A3)*(DataSheet!$AT:$AT=Statistics!B3),DataSheet!$BC:$BC)) Ctrl Shift Enter

Since this is an array formula, you will want to limit your ranges instead of using whole column references (i.e. change $S:$S to something like $S$2:$S$1000).

Ah, yes- totally a syntax problem.

Thank you, best regards

You're welcome. Happy to help.