standard deviation calculation for multiple criteria

[mre03c]

im trying to find the standard deviation of a bunch of subjects in a population.

i have 1 sheet which has three columns: date, the subject, and number. this sheet is updated daily. the dates and subject names are listed through the end of the trial, while the numbers are manually updated daily.


i have a sheet with all the subject's names, and im trying to get a formula to tell me the standard deviation of each subject except for the values i havent filled in yet.

everything ive tried has given me an error message so maybe somebody can help me. thanks!

 

[Aladin Akyurek]

im trying to find the standard deviation of a bunch of subjects in a population.

i have 1 sheet which has three columns: date, the subject, and number. this sheet is updated daily. the dates and subject names are listed through the end of the trial, while the numbers are manually updated daily.


i have a sheet with all the subject's names, and im trying to get a formula to tell me the standard deviation of each subject except for the values i havent filled in yet.

everything ive tried has given me an error message so maybe somebody can help me. thanks!

Let Sheet1 house the data.

Sheet2

A2 houses a subject's name...

B2:

Control+shift+enter, not just enter...

One of:

=STDEV(IF(Sheet1!$B$2:$B$13=I2,Sheet1!$C$2:$C$13))

=STDEV(IF(Sheet1!$B$2:$B$13=I2,IF(ISNUMBER(Sheet1!$C$2:$C$13),Sheet1!$C$2:$C$13)))

Adjust to suit. Note that some prefer to invoke STDEVP instead of STDEV.

 

[barry houdini]

If your data is in a sheet called data with dates in column A subjects in B and numbers in C, and then in another sheet you have the subjects in A2 down then try this formula in B2

=STDEV(IF('Data'!B$2:B$1000=A2,IF('Data'!C$2:C$1000<>"",'Data'!C$2:C$1000)))

confirmed with CTRL+SHIFT+ENTER and copied down the column

adjust ranges as required

 

[taigovinda]

Welcome to the board! You can use a pivot table to do this.

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Subject	Number
2	12/17/2009	1	36
3	12/17/2009	2	68			StdDev of Number
4	12/17/2009	3	1			Subject	Total
5	12/17/2009	4	98			1	41.01219331
6	12/19/2009	1	94			2	15.55634919
7	12/19/2009	2	90			3	38.89087297
8	12/19/2009	3	56			4	0.707106781
9	12/19/2009	4	97			Grand Total	34.96120299
10	12/20/2009	1
11	12/20/2009	2
12	12/20/2009	3
13	12/20/2009	4
1

Excel 2003



The pivot table is on the right. Select all of the data, including the rows that are still blank. Then use insert->pivot table, then put number in the data field and subject in the row field. Right-click on cell F3, which will say "count of number" and choose field settings. Then, where "count" is selected, change it to standard deviation... When you add data, you will need to right-click the pivot table and choose refresh.

Hope that helps, post back with problems.

Tai