Standand Deviation as array

[Francisco]

Hi everyone,
Today I have an issue that I do not find a solution.
I have the following summary list:

CompID N. Staff
1001 5
1002 3
1003 2
1004 1

The first column is company ID and the second the number of staff working on
that company, if appplying STDEV(N. Staff) I obtain 1.707825.
So far so good.

I have the extended list from above:

CompID StaffID
1001 67
1001 2
1001 89
1001 22
1001 98
1002 70
1002 65
1002 9
1003 29
1003 97
1004 63

So, for the company 1001 there are 5 individuals, etc.

Based on the second list (this is key) I need to calculate the STDEV that
give me the result, i.e. 1.707825

I have try different things with array formulas but it does not work for me.
Please could anyway suggest me the sytax of such formula (if possible).

Many thanks in advance.
F

 

[Mike H]

Hi,

On your second table you say you want a standard deviation of 1.707825 for
1001 but the standard deviation of that data is 41.96784 so I assume that's a
typo. To get the SD of 1001 use this array formula

=STDEV(IF(A2:A13=1001,B2:B13))

This is an array formula which must be entered by pressing 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 formula
'you must enter it again with CTRL+Shift+Enter.



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Francisco]

Hi Mike,
It was correct,
The calcuation is done on the second column, ie STDEV(N. Staff), therefore
I am after 1.707825 on the second list.

 

[Mike H]

Hi,

I am now (not unusual at my age) confused. Your second list looks like this

CompID StaffID
1001 67
1001 2
1001 89
1001 22
1001 98
1002 70
1002 65
1002 9
1003 29
1003 97
1004 63

Unless I'm completely missing the point your original question asked for the
SD of CompID 1001 and for that company we have 5 numbers
67,2,89,22,98 and the SD of those is 41.96784. What am I missing?

Which numbers are we calculating the SD of and what is the criteria for
selecting those numbers?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Per Jessen]

Hi,

I do not think this can be done without a summary table. Suppose you have a
list of unique CompID's in D25, then insert this formula in E2 and copy it
down:

=countif(A$2:A$12,D2)

Note that you can use the advanced filter function to extract a list of
unique CompID's, if needed.

Regards,
Per

 

[Francisco]

Mike,
Many thanks for you time.
It is my fault I do not expressed myself very well.

I have 4 companies, each one has a different staff number:

List1:
CompID StaffID
1001 67
1001 2
1001 89
1001 22
1001 98
1002 70
1002 65
1002 9
1003 29
1003 97
1004 63

Company 1 (1001), has 5 people,
Company 2 (1002) has 3 people,
.... and so on.

So I have created a summary by Number of staff, the list look like this:

List2
CompID NoStaff(count StaffID)
1001 5
1002 3
1002 2
1002 1

The STDEV on the summary (list2) is STDEV(B2:B5)=1.707885

The problem I have is I can not calculate the STDEV based on List2, I need
to work on List1 for another reasons. I asume I need to have an array formula
to group then number of staff by Company on the fly, and then calculate STDEV.
The question is, how can I do this on a single formula? The final result
must be 1.707885.

The formula will look like ={STDEV(No Staff group by CompanyID)}

 

[Francisco]

Done!
=STDEV(IF(FREQUENCY(A2:A12,A2:A12)<>0,FREQUENCY(A2:A12,A2:A12)))

Guys thanks a lot for input.