Standard Deviation.

[deuce]

Hello, I am trying to create the first standard deviation of a series of numbers starting from the modal value. I have a method to accomplish this but lack the technical excel expertise to do it. Can anyone please examine this and help me to create a formula to obtain the upper and lower limits of the first standard deviation using the method highlighted below?

* From AB29 downwards I have numbered values going from max to min, from AC29 I have the number of counts for each value in AC29.

The Method is :

1) Sum up the total number of counts from AC29. Output this result in S31.

2) Take 68% or 70% of this total number of counts and output this count result to S32, the percent value(68%,70%) is typed S33 and this percent value value can change.

3) Identify the Modal Value which is already calculated by a formula in cell S29.

4) Add The counts of 2 numbers above and 2 below the modal value, meaning add the counts of the 2 values above the modal value then add the counts of the 2 values below the modal value.

5) The counts of the pair of numbers with the greater number should be added to the counts of the modal value.

We continue this procedure until the total number of counts reaches the percentage value stated in point number 2.

In outputting the result we get the upper number and lower number limits of the 1st standard deviation counting from the modal value. the upper limit number would be outputted to cell S35 and the lower limit number would be outputted to cell S36.


Here is an example with the expected result :

# C
1083 1
1082 2
1081 2
1080 4
1079 4
1078 4
1077 4
1076 5
1075 7
1074 9
1073 11
1072 10
1071 10
1070 9
1069 9
1068 8
1067 6
1066 6
1065 3
1064 3
1063 3
1062 2
1061 2
1060 2
1059 2
1058 2
1048 1


Total Counts = 131

70% of total counts = 91.7

Counts of the modal Value = 11

Pair counts : 11 + 20 + 18 + 16 + 14 + 9 + 6 = 94

Upper standard deviation limit number: 1080

Lower standard deviation limit number: 1074

Hope someone can help me out in this.

thanks.

 

[SydneyGeek]

Is there a special reason for doing the work manually?

By using the original dataset you can determine the mean, median, mode and standard deviation using built-in Excel functions. The functions are AVERAGE, MEDIAN, MODE and STDEV (or STDEVP for populations). Check the online Help; the functions changed for Excel 2010 but are the same for earlier versions. You *may* need to activate the Analysis Toolpak to get to some of the functions.

Once you have the Mode and the Stdev, you can easily get the upper and lower bounds of the first, second and third standard deviations.

Denis

 

[deuce]

hi, it has to be done with the method I described. Can you help me to create the formula please?

if you can use the built in functions to give the correct results as in the example then can you please show me how.

 

[SydneyGeek]

Assuming that the values are in Column A and the counts are in Column B, this will give the result. It's quite roundabout because I've aattempted to lay out the logic.

Excel Workbook
	D	E	F	G
1	Total count	131
2	Max Count	11
3	Value with max count	1073	B11
4	Cutoff	70%
5	Target count	91.7
6	Mode		11	11
7	Offsets	1	19	30
8		2	17	47
9		3	14	61
10		4	13	74
11		5	12	86
12		6	10	96
13		7	10	106
14		8	5	111
15		9	5	116
16		10	4	120
17
18	Count just > cutoff	96
19	Offset just > cutoff	7
20	Upper limit	1080
21	Lower limit	1066
Sheet1

Denis

 

[deuce]

thanks for the effort, Can you simplify the formula? I suggest you examine the method carefully and ask any questions for clarifications.

thanks.

 

[SydneyGeek]

Which part of it is incorrect?
If you want formulas that intelligently pick up the mode, and its value and address, you need something like what I gave you.
It's easy to give you a formula that exactly mirrors your description for this data set. It's unlikely to work for the next dataset.

Denis

 

[deuce]

Hi thanks for the response, what do you think can be done to derive the upper and lower limits? and yes your right the formula has to work on all the data sets that I put into it.

In a bit of confusion about how to do this really.

thanks.

 

[SydneyGeek]

Let's see if it helps to describe the layout. E1:E5 contain exactly what the labels in D1:D5 say.
F3 has the *address* of the cell with the mode.

E7:E16 are just the numbers 1-10, representing distance from the mode.
F7:F16 are the pairwise sums going out from the mode cell.
G7:G16 is the running sum, to tell when you have got past the target value.

D18:E21 are the summary cells, including the upper and lower limits of the first SD.

Denis

 

[deuce]

hi denis, i have 5000 numbers/cells going down along with their counts from the dataset, will this formula work without modification for each dataset or i would need to make modifications to the formula each time i add a new dataset.

 

[SydneyGeek]

You will need to adjust the ranges. In this case you have values in rows 1:27. If the number of rows changes, adjust the formulas in E1:F5 (in the example I gave).

Denis