Calculating STDEV and other Calcs based on another cells value
 

Hello,

I am trying to calculate a standard deviation on a column if another column
contains a certain value. I have been able to calculate the average using
the AverageIFS function, but can't find a way to do it for the stdev
function. For example, if there are 3 people throwing a base ball and I have
entered 20 data points for each. I would like to calculate the standard
deviation based on if the name equals "Joe, "Bob" or "Scott".

Thanks,

Scott

Calculating STDEV and other Calcs based on another cells value
 

Names in the range A1:A10
Numeric values in the range B1:10

Entered as an array** :

=STDEV(IF(ISNUMBER(MATCH(A1:A10,{"joe","bob","scot t"},0)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Scott" <Scott @discussions.microsoft.com wrote in message
...
Hello,

I am trying to calculate a standard deviation on a column if another
column
contains a certain value. I have been able to calculate the average using
the AverageIFS function, but can't find a way to do it for the stdev
function. For example, if there are 3 people throwing a base ball and I
have
entered 20 data points for each. I would like to calculate the standard
deviation based on if the name equals "Joe, "Bob" or "Scott".

Thanks,

Scott

Calculating STDEV and other Calcs based on another cells value
 

Thanks T. Valko, What if I want to match two columns (Team and pitcher)?

Thanks in advance,

Scott

"T. Valko" wrote:

Names in the range A1:A10
Numeric values in the range B1:10

Entered as an array** :

=STDEV(IF(ISNUMBER(MATCH(A1:A10,{"joe","bob","scot t"},0)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Scott" <Scott @discussions.microsoft.com wrote in message
...
Hello,

I am trying to calculate a standard deviation on a column if another
column
contains a certain value. I have been able to calculate the average using
the AverageIFS function, but can't find a way to do it for the stdev
function. For example, if there are 3 people throwing a base ball and I
have
entered 20 data points for each. I would like to calculate the standard
deviation based on if the name equals "Joe, "Bob" or "Scott".

Thanks,

Scott

Calculating STDEV and other Calcs based on another cells value
 

=STDEV(IF((Team="Pirates")*(ISNUMBER(MATCH(Pitcher ,{"joe","bob","scott"},0))),B1:B10))

Where Team and Pitcher are named ranges.

Don't forget to array enter!


--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
Thanks T. Valko, What if I want to match two columns (Team and pitcher)?

Thanks in advance,

Scott

"T. Valko" wrote:

Names in the range A1:A10
Numeric values in the range B1:10

Entered as an array** :

=STDEV(IF(ISNUMBER(MATCH(A1:A10,{"joe","bob","scot t"},0)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Scott" <Scott @discussions.microsoft.com wrote in message
...
Hello,

I am trying to calculate a standard deviation on a column if another
column
contains a certain value. I have been able to calculate the average
using
the AverageIFS function, but can't find a way to do it for the stdev
function. For example, if there are 3 people throwing a base ball and
I
have
entered 20 data points for each. I would like to calculate the
standard
deviation based on if the name equals "Joe, "Bob" or "Scott".

Thanks,

Scott