calculate the stdev of an array using text as the criteria
 

I'm trying to calculate the stdev of a tester's results and compare it with
other testers.
B4:B15 has the testers' names
C$4:C15 has the test results that I want to calculate the Stdev for.

If I put in =Sum(IF(B$4:B15="DM",C$4:C15,0)), I get the right sum
If I put in =STDEV(IF(B$4:B15="DM",C$4:C15,0)), I get a number that is
definitely not the Stdev

I am trying to avoid having to go and individually select each test result
for each tester because the cells will have to be updated each time a tester
enters a value.

If you could help, that would be great.

Thanks.

calculate the stdev of an array using text as the criteria
 

Hi,

Your formula was nearly correct try this array entered

=STDEV(IF(B$4:B15="DM",C$4:C15))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Crypes" wrote:

I'm trying to calculate the stdev of a tester's results and compare it with
other testers.
B4:B15 has the testers' names
C$4:C15 has the test results that I want to calculate the Stdev for.

If I put in =Sum(IF(B$4:B15="DM",C$4:C15,0)), I get the right sum
If I put in =STDEV(IF(B$4:B15="DM",C$4:C15,0)), I get a number that is
definitely not the Stdev

I am trying to avoid having to go and individually select each test result
for each tester because the cells will have to be updated each time a tester
enters a value.

If you could help, that would be great.

Thanks.

calculate the stdev of an array using text as the criteria
 

I should have explained that the reason your formula didn't work was because
you gave it a false option for when the column b didn't = "DM" and it was
using that zero option in the STDEV calculation when DM = FALSE

leaving out the FALSE option makes it evaluate nothing when DM =FALSE

Mike


"Mike H" wrote:

Hi,

Your formula was nearly correct try this array entered

=STDEV(IF(B$4:B15="DM",C$4:C15))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Crypes" wrote:

I'm trying to calculate the stdev of a tester's results and compare it with
other testers.
B4:B15 has the testers' names
C$4:C15 has the test results that I want to calculate the Stdev for.

If I put in =Sum(IF(B$4:B15="DM",C$4:C15,0)), I get the right sum
If I put in =STDEV(IF(B$4:B15="DM",C$4:C15,0)), I get a number that is
definitely not the Stdev

I am trying to avoid having to go and individually select each test result
for each tester because the cells will have to be updated each time a tester
enters a value.

If you could help, that would be great.

Thanks.

calculate the stdev of an array using text as the criteria
 

Hi,

In other words you should change your formula to:

=STDEV(IF(B$4:B15="DM",C$4:C15,""))
or
=STDEV(IF(B$4:B15="DM",C$4:C15))

because

=STDEV(IF(B$4:B15="DM",C$4:C15,))
or
=STDEV(IF(B$4:B15="DM",C$4:C15,0))

will return incorrect results.

All of these are Array entered - Ctrl+Shift+Enter
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike H" wrote:

I should have explained that the reason your formula didn't work was because
you gave it a false option for when the column b didn't = "DM" and it was
using that zero option in the STDEV calculation when DM = FALSE

leaving out the FALSE option makes it evaluate nothing when DM =FALSE

Mike


"Mike H" wrote:

Hi,

Your formula was nearly correct try this array entered

=STDEV(IF(B$4:B15="DM",C$4:C15))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Crypes" wrote:

I'm trying to calculate the stdev of a tester's results and compare it with
other testers.
B4:B15 has the testers' names
C$4:C15 has the test results that I want to calculate the Stdev for.

If I put in =Sum(IF(B$4:B15="DM",C$4:C15,0)), I get the right sum
If I put in =STDEV(IF(B$4:B15="DM",C$4:C15,0)), I get a number that is
definitely not the Stdev

I am trying to avoid having to go and individually select each test result
for each tester because the cells will have to be updated each time a tester
enters a value.

If you could help, that would be great.

Thanks.

calculate the stdev of an array using text as the criteria
 

Isn't that what Mike already said?

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

In other words you should change your formula to:

=STDEV(IF(B$4:B15="DM",C$4:C15,""))
or
=STDEV(IF(B$4:B15="DM",C$4:C15))

because

=STDEV(IF(B$4:B15="DM",C$4:C15,))
or
=STDEV(IF(B$4:B15="DM",C$4:C15,0))

will return incorrect results.

All of these are Array entered - Ctrl+Shift+Enter
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike H" wrote:

I should have explained that the reason your formula didn't work was
because
you gave it a false option for when the column b didn't = "DM" and it was
using that zero option in the STDEV calculation when DM = FALSE

leaving out the FALSE option makes it evaluate nothing when DM =FALSE

Mike


"Mike H" wrote:

Hi,

Your formula was nearly correct try this array entered

=STDEV(IF(B$4:B15="DM",C$4:C15))

This is an array formula which must be entered with CTRL+Shift+Enter
and NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Crypes" wrote:

I'm trying to calculate the stdev of a tester's results and compare
it with
other testers.
B4:B15 has the testers' names
C$4:C15 has the test results that I want to calculate the Stdev for.

If I put in =Sum(IF(B$4:B15="DM",C$4:C15,0)), I get the right sum
If I put in =STDEV(IF(B$4:B15="DM",C$4:C15,0)), I get a number that
is
definitely not the Stdev

I am trying to avoid having to go and individually select each test
result
for each tester because the cells will have to be updated each time a
tester
enters a value.

If you could help, that would be great.

Thanks.