Standand Deviation as array

  • Thread starter Thread starter Francisco
  • Start date Start date
F

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
 
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.
 
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.
 
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.
 
Hi,

I do not think this can be done without a summary table. Suppose you have a
list of unique CompID's in D2:D5, 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
 
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)}
 
Done!
=STDEV(IF(FREQUENCY(A2:A12,A2:A12)<>0,FREQUENCY(A2:A12,A2:A12)))

Guys thanks a lot for input.
 
Back
Top