| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| standard deviation | Excel Worksheet Functions | |||
| Standard Deviation | Excel Worksheet Functions | |||
| Standard Deviation | Excel Worksheet Functions | |||
| standard deviation | Excel Discussion (Misc queries) | |||
| Histrogramms: Calculating average and standard deviation | Excel Worksheet Functions | |||
Threaded Mode
