Standand Deviation as array
Done!
=STDEV(IF(FREQUENCY(A2:A12,A2:A12)<0,FREQUENCY(A2 :A12,A2:A12)))
Guys thanks a lot for input.
"Francisco" wrote:
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)}
"Mike H" wrote:
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.
"Francisco" wrote:
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" wrote:
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" wrote:
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
|