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.

 

[deuce]

hi, i tried editing the ranges but get confused on the formula as it has too many conditions.

I marvel at your skill but can you create another formula that is simple to comprehend and implement in my sheet?

 

[deuce]

hi, I am working with the formula table that was given and find it to be good however I get an error because the formula in cell F3 gives the incorrect address, but when I do type in the correct answer into F3, the formula outputs results.

Can this F3 formula be fixed?

thanks and regards

 

[SydneyGeek]

Could you say specifically what is wrong with the result?
Is it pointed at the wrong cells?
Would you like it to be in a different cell? If so, which one?
And how big is the 'real' range that you are working with?

Denis

 

[deuce]

Hi Denis, thanks for your response, what is wrong with the result is that the mode is in cell x and the F3 output gives the address of cell y in the same column referring to the cell which is not the one with the highest counts and correlating to the mode.

the mode is already calculated by a different formula and takes into consideration multiple numbers with the same amount of counts (if that were to occur) so in the cell which contained your formula for the mode i just put in = cell with mode address.

Yes, It's pointed at the wrong address.

the cell range i am dealing with is AB for numbers, AC for counts, AE30 for the highest count result from your formula and AF31 for the cell address (F3 formula).

The real range always differs on each day up to 10000 cells in the AB;AC column and has to be adjusted manually everyday.

maybe something can be suggested.

look at the mode in in cell AE31, output it's counts in cell AE30, then find and output the address that has the correct cell details.

thanks.
deuce

 

[deuce]

hi denis, i was just tinkering with the F3 formula and fixed it. the problem was being caused by putting in an incorrect lookup array meaning I started from the first cell that had the data which caused an incorrect address output. when the lookup array was changed to reflect the entire column starting from AC1 i got the correct result.

thanks a million, i will continue to test out the formula.

i marvel at your skill.

 

[deuce]

Hi, old thread but i was still subscribed to it. I have problems with the formula as it gives looping errors(#REF!) when applied to a range, by looping errors it means from 1 cell leading to another cell and then to another cell and so forth.

Is there another set of formulas that can accomplish my task without giving errors?