Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 27th 07, 05:36 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 51
Default Calculating Conditional Standard Deviation?

Hi there,

I have two columns - Industry Code and Market Cap. I want to create a
summary table for average market cap and standard deviation for certain
Industries. However, there are like 20 different industry codes and I didn't
exactly feel like doing it manually for 20 cells. I tried out this formula
first:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}

Using it an array - the Ctrl+Shift+Enter thing. That didn't work out too well.

Cells E2:E296 contained the Industry Code column. Cells F2:F296 contained
the market capitlisations, while K17 was one of the 20 industry codes.

I think the reason why it didn't work (apart from probably wrong usage of
the double negatives on my part) was that it produced zeros for the companies
that I didn't want to include, which could screw up the STDEV calc, since it
includes any zeros in the calculations.

Is there any way around this?

I thought there might be another way by using standard deviation = square
root (expected value of x^2 - (average of x)^2 ) but couldn't really figure
out how to do a conditional squared sum. Is there of a way of doing that?

Thanks in advance,

Iwan J

  #2   Report Post  
Old June 27th 07, 05:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 51
Default Calculating Conditional Standard Deviation?

Nevermind, I figured out a way by using OFFSET.

However, I would still be curious if there was another way of doing this...

"Harimau" wrote:

Hi there,

I have two columns - Industry Code and Market Cap. I want to create a
summary table for average market cap and standard deviation for certain
Industries. However, there are like 20 different industry codes and I didn't
exactly feel like doing it manually for 20 cells. I tried out this formula
first:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}

Using it an array - the Ctrl+Shift+Enter thing. That didn't work out too well.

Cells E2:E296 contained the Industry Code column. Cells F2:F296 contained
the market capitlisations, while K17 was one of the 20 industry codes.

I think the reason why it didn't work (apart from probably wrong usage of
the double negatives on my part) was that it produced zeros for the companies
that I didn't want to include, which could screw up the STDEV calc, since it
includes any zeros in the calculations.

Is there any way around this?

I thought there might be another way by using standard deviation = square
root (expected value of x^2 - (average of x)^2 ) but couldn't really figure
out how to do a conditional squared sum. Is there of a way of doing that?

Thanks in advance,

Iwan J

  #3   Report Post  
Old June 27th 07, 05:57 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Calculating Conditional Standard Deviation?

Try it like this: (array entered)

=STDEV(IF($E$2:$E$296=K17,$F$2:$F$296/1000000))

Biff

"Harimau" wrote in message
...
Hi there,

I have two columns - Industry Code and Market Cap. I want to create a
summary table for average market cap and standard deviation for certain
Industries. However, there are like 20 different industry codes and I
didn't
exactly feel like doing it manually for 20 cells. I tried out this formula
first:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}

Using it an array - the Ctrl+Shift+Enter thing. That didn't work out too
well.

Cells E2:E296 contained the Industry Code column. Cells F2:F296 contained
the market capitlisations, while K17 was one of the 20 industry codes.

I think the reason why it didn't work (apart from probably wrong usage of
the double negatives on my part) was that it produced zeros for the
companies
that I didn't want to include, which could screw up the STDEV calc, since
it
includes any zeros in the calculations.

Is there any way around this?

I thought there might be another way by using standard deviation = square
root (expected value of x^2 - (average of x)^2 ) but couldn't really
figure
out how to do a conditional squared sum. Is there of a way of doing that?

Thanks in advance,

Iwan J



  #4   Report Post  
Old June 27th 07, 06:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 51
Default Calculating Conditional Standard Deviation?

It didn't work, unfortunately.

"T. Valko" wrote:

Try it like this: (array entered)

=STDEV(IF($E$2:$E$296=K17,$F$2:$F$296/1000000))

Biff

"Harimau" wrote in message
...
Hi there,

I have two columns - Industry Code and Market Cap. I want to create a
summary table for average market cap and standard deviation for certain
Industries. However, there are like 20 different industry codes and I
didn't
exactly feel like doing it manually for 20 cells. I tried out this formula
first:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}

Using it an array - the Ctrl+Shift+Enter thing. That didn't work out too
well.

Cells E2:E296 contained the Industry Code column. Cells F2:F296 contained
the market capitlisations, while K17 was one of the 20 industry codes.

I think the reason why it didn't work (apart from probably wrong usage of
the double negatives on my part) was that it produced zeros for the
companies
that I didn't want to include, which could screw up the STDEV calc, since
it
includes any zeros in the calculations.

Is there any way around this?

I thought there might be another way by using standard deviation = square
root (expected value of x^2 - (average of x)^2 ) but couldn't really
figure
out how to do a conditional squared sum. Is there of a way of doing that?

Thanks in advance,

Iwan J




  #5   Report Post  
Old June 27th 07, 07:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Calculating Conditional Standard Deviation?

Hmmm....

Well, it was the corrected equivalent of:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}


You were correct in your assumption about producing 0's and skewing the
result.

Just multiplying: ($E$2:$E$296=K17)*$F$2:$F$296/1000000

Will produce the 0's so the double unary in this case was redundant.

The syntax I used takes care of that.

Biff

"Harimau" wrote in message
...
It didn't work, unfortunately.

"T. Valko" wrote:

Try it like this: (array entered)

=STDEV(IF($E$2:$E$296=K17,$F$2:$F$296/1000000))

Biff

"Harimau" wrote in message
...
Hi there,

I have two columns - Industry Code and Market Cap. I want to create a
summary table for average market cap and standard deviation for certain
Industries. However, there are like 20 different industry codes and I
didn't
exactly feel like doing it manually for 20 cells. I tried out this
formula
first:

{=STDEV((--($E$2:$E$296=K17))*$F$2:$F$296/1000000)}

Using it an array - the Ctrl+Shift+Enter thing. That didn't work out
too
well.

Cells E2:E296 contained the Industry Code column. Cells F2:F296
contained
the market capitlisations, while K17 was one of the 20 industry codes.

I think the reason why it didn't work (apart from probably wrong usage
of
the double negatives on my part) was that it produced zeros for the
companies
that I didn't want to include, which could screw up the STDEV calc,
since
it
includes any zeros in the calculations.

Is there any way around this?

I thought there might be another way by using standard deviation =
square
root (expected value of x^2 - (average of x)^2 ) but couldn't really
figure
out how to do a conditional squared sum. Is there of a way of doing
that?

Thanks in advance,

Iwan J








  #6   Report Post  
Old June 27th 07, 01:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default Calculating Conditional Standard Deviation?

Please clarify the meaning of "It didn't work". If array entered, the
formula will produce the standard deviation of the values in column F divided
by 1000000 that correspond to values in column E which equal K17. Was the
formula not array entered, or was that standard deviation not what you
wanted?

Another possibility is that there may be issues with the matching to K17.
IF K17 appears to be a number, either it or some of the "matching values" may
be a string instead of a number, an therefore not a match. If K17 is
supposed to be a string, there may be differences in trailing spaces that
prevent a match.

Jerry

"Harimau" wrote:

It didn't work, unfortunately.

"T. Valko" wrote:

Try it like this: (array entered)

=STDEV(IF($E$2:$E$296=K17,$F$2:$F$296/1000000))

Biff



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
standard deviation ckatz Excel Worksheet Functions 1 October 25th 06 08:31 PM
Standard Deviation Stan Banner Excel Worksheet Functions 1 March 12th 06 12:54 AM
Standard Deviation Stan Banner Excel Worksheet Functions 1 March 11th 06 09:11 PM
standard deviation Chris Excel Discussion (Misc queries) 1 October 13th 05 04:52 AM
Histrogramms: Calculating average and standard deviation Jens Eichelbaum Excel Worksheet Functions 2 November 23rd 04 02:10 AM


All times are GMT +1. The time now is 06:36 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017