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