Conditional Formatting not working with negative numbers in Excel

Question

I am trying to track relative performance of different mutual funds, by their % returns.  I have set up formulas for the average, sd, avg+sd, and avg-sd.  Then I set up conditional formatting such that if a cells is >avg+sd, it's shaded green.  If >avg, it's shaded pale green.  If <avg, it's shaded pale orange.  If <avg-sd, it's shaded orange.  It seems to work just fine, if the cell is positive.  But if the cell is negative, then the formatting doesn't seem to work -- all cells get the green shading. 

Here is a screen shot.  Can you tell what I am doing wrong? 

Thanks, Sarah

Answer 1

Thanks, Bernie, for taking the time to respond.  My values are actual numbers.  I tried your suggestion anyway, and it stripped all the conditional formatting from the negative numbers.

So, while your suggesting didn't fit it directly, it did get me on the right path -- suspecting that the format of the numbers was somehow to blame.  I reformatted from Percentage to Number, and only the positive numbers switched format.  The negative numbers still showed as percentages.  I fixed the problem by retyping the negative numbers (they had been copied off the Vanguard website and pasted into Excel).  Not sure why that was an issue, but at least I have a solution.

Thanks,

Sarah

Answer 2

Hi,

Looking to your table... Maybe the minus sign is not the minus sign. It looks more to ascii 6 then ascii 45.

You can test it with e.g. =CHAR(A6), if it does not return 45 but 150, it is not the minus sign. Select your data, find replace, in the find box type while pressing the ALT-key, 0150, replace by minus.

Before

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

–1%

After

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

-1%

Answer 3

It would help if you posted the workbook, but I suspect that it is because the values are strings and not actual numbers.  Try changing all references to J13 in your CF formulas to VALUE(J13).  The AVERAGE function will auto-convert strings to values, but individual cell references will not.

Answer 4

Thanks, JP.  That may well have been the problem.  See my note above to Bernie about how I ended up fixing it.

Answer 5

Hi,

I saw your note, would give a quick way iso retyping...