Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 H" skrev i meddelelsen ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standand Deviation as array
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard deviation | Excel Discussion (Misc queries) | |||
standard deviation | Charts and Charting in Excel | |||
Standard deviation | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formulas for Geometric Standard Deviation and Sharpe | Excel Worksheet Functions |