Conditional Formatting and Standard Deviation

[dlamarche]

Hello all,

I am trying to see how the Conditional Formatting feature works in Excel 2007 using the Above or below average rule type. I am not a statistician but I like numbers and I also teach private lessons of Excel.

The first four about the average are very self explanatory but the last six one are causing me some trouble understanding. This is what my research seemed to have demonstrated up to now:

The Empirical rule (some times called the 68-95-99.7 rule) states that 68% of all values in a normally distributed dataset will fall inside (plus or minus) 1 standard deviation from the mean right? So this would be 34% from each side of the average.

Then 95% would fall inside 2 std dev from the mean.

So if I understand well selecting "1 std dev above" the average for the selected range rule in Conditional formatting should highlight about 1/3rd (half of 68%) or the values above the average (if the data is normally distributed right again?

And 1 std dev below would format about 1/3rd of the values under the mean.

I tried with a series of numbers having an average of 0 and a StDev of 1 to see this for myself and it does not seem to add-up.

What I did was to create a series of numbers between -3 to +3 with an increment of 0.05. This was to cover 3 standard deviation in a bell curve.

Then for each value I used the NORMALDIST() function with an average of 0 and a StDev of 1. Charting this gave me a beautiful bell curve.

When I used the Conditional Formatting using the "1 std dev above" option it formatted about 13 values above and below the average (which is 0). I expected about 1/3rd of the values above the average only to be highlighted.

Obviously I am not an analyst or a math guru but anyone can kindly explain to me where i am going wrong in my reasoning?

Thanks

 

[venkat1926]

just a off hand remark. it is within AVERAGE PLUS ONE standard deviation will be 1/3rd of data. Please also remember that formula is for contious distribution where as your data is discrete. Still I expect it will be near abot one third .

suppose average is x and standard deviation is y
then condidtionformat is
cell is >x and less than x+y

 

[dlamarche]

Thank for your reply venkat1926,

I was getting slightly worried that my question was too hard.

I did understand about the 'between the average and 1 stdev above' and the 'between the average and 1 stdev below' part. Thanks for confirming this to me.

Now I am wondering why it is that the values highlighted were both above AND below the average when the Conditional Formatting option says '1 StDev above' and '1 StDev below'.

Now I may not understand the purpose of this command. You mentioned something about that function is for contious (maybe it was continuous) distribution whereas mine was discreet.

Would you be so kind as to explain or give an short example of what is a continuous distribution, I am not sure to understand. Is it like ... linear (1, 2, 3, 4, ... ,195, 196) where the numbers always increase or decrease in a linear fashion?

Thanks for your time venkat1926

 

[Legacy 14611]

You can look up continuous distributions on Google if you like.

But for a short answer, continuous distributions are distributions of continuous random variables, discrete distrs are distributions of discrete variables.

Continuous = taking any value in a specified range on the real line (or hyperplane if you're considering multivariate distributions).
Discrete = taking on only values in prespecified finite steps, like 1,2,3 ...infinity. (videbook of this name by Lancelot Hogben)

For more precise definitions you need to go into mathematical stuff, although even mathematicians took a long time historically to come to grips with this stuff. Zeno of Elea was a Greek logician who baffled Aristotle and others with a collection of puzzles or paradoxes on this distinction. (Like the arrow paradox - "If the tip of the arrow is always in the instant, then the moving arrow is unmoved", with further explanation you can look up if you like.

A statistical distribution is actually a distribution of probabilities, not of numbers, although typically investigators take samples, which are specific outcomes usually measured discretely, and try to use the combination of samples with the underlying probability distributions to find out more about the problem in hand. This is essentially what statistics as usually understood is about (although the name "statistics" is derived from numbers relating to the affairs of the state - meaning populations, tax collections etc.)

I can't help you with the conditional formatting command because I don't have excel 2007. From your post, I doubt that your difficulty arises from the difference between continuous and discrete distributions. I would first investigate either your using the conditional command wrongly or possibly a bug in the command itself.

However, you did seem to be trying to approximate a continuous distribution by using discrete value and I suppose one could evaluate the probability of you getting the outcome you did with the methods you followed even if there are no errors in your commands or bugs in the conditional formatting. Is that appropriate for a forum like this?

v sorry, but not having Excel 2007 to replicate your procedures, I can't be of much help.

Continuous distribution. Example is the normal, Gaussian, bell curve, de Moivre, or whatever your background called it. Like the length of a piece of string.
Discrete distribution, simplest is maybe the binomial distribution, like will your tossed coin turn out a head or a tail? or the Poisson distribution, like the numbers of soldiers on a preselected cannon who were kicked by horses in the Crimean war, etc.

 

[venkat1926]

what did you type in conditional formatting window?. see my example