Condtional Formatting and Standard Deviation

[Steve]

Trying to ascertain if there is a bug in Excel 2007, or I'm misinterpreting
how "Conditional Formatting" works.

In short, I have a series of investment returns where I want to check for
outliers beyond two and three standard deviations. I used the "Conditional
Formatting" style box, and highlighted the cells appropriately.

For one population, the mean is 2.55 with a standard deviation of .73 Thus,
when I'm checking for +/- two standard deviations, values less than 1.09
(2.55 - 2*.73) and greater than 4.01 (2.55 + 2*.73) should show up with their
respective formatting.

The odd part is that values that are between the lower bound (1.09) and the
mean (2.55) incorrectly are highlighted. But this does not happen on the
higher side of the mean. Secondly, even if I change the criteria to one,
two, or three standard deviations below average, the same cells get
highlighted.

Am I misunderstanding how the Excel conditional formatting works, or is
there a bug?

Kind regards,
Steve

 

[Herbert Seidenberg]

Excel 2007 PivotTable
PT type Histogram.
PivotChart with color coded StDev.
With macro.
Source has CF.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_16_10.xlsm

 

[Steve]

I'm using Excel 2007 and their conditional formatting tab. Within it, the user can format cells based upon the value being 1, 2, or 3 standard deviations above/below the mean. Theoretically, Excel should be calculating the mean and standard deviation, but it appears there could be an error in the internal calculations

In my case, I'm formatting cells if they are 2 or 3 standard deviations from the mean (different color schemes for each).