Conditional formatting problem in Excel 12 / 2007

[Chip Taylor]

I'm having a problem with conditional formatting in an Excel 2007 pivot table.

The spreadsheet has a calculated field column which is the % difference in
revenue from one month to the next. After I select the cells in that column
and make a new formatting rule for Icon Sets, the arrows/colors are not
lining up with the values in the Rule Description.

Here are the rules:

Green (up arrow) when value is >= 10 Percent
Yellow (sideways arrow) when <10 and >= 1 Percent
Red (down arrow) when < 1

After I apply the rule, everything is green. From 250% through -150%, every
arrow is green and pointing up.

Thanks for your help.

ChipTay

 

[Roger Govier]

Hi Chip

Works fine for me.
Mark the cells again>Conditional Formatting>Manage Rules>Edit
and check the values again to ensure it has got

= in first box, 10 in second box, percent in 3rd box
= in 4th box, 1 in 5th box, percent in 6th box

No option for setting the final values, but is should show as when <1 for
the Red down arrow.

 

[Chip Taylor]

Thanks for your reply Roger. Unfortunately, that's exactly how the settings
are too.

This is the only conditional formatting rule in the spreadsheet, too.

Best,

ChipTay

 

[Roger Govier]

Hi Chip

If you want to mail me a copy of the sheet, I will take a look.
Send to
roger at technology4u dot co dot uk
Do the obvious with at and dot.

 

[Roger Govier]

Hi Chip

File received and returned to you.

There does appear to be a bug when using icon sets and using percentage
values, especially when there are negative percentages.
Changing the criteria to Numbers and using decimal equivalents of the
percentage values gets round the problem and produces the desired result.

I used 0.1 in 1st box, Number in second
0 in 3rd box and Number in 4th

Values above 10% show Green arrow pointing upward, between 0% and 10% Yellow
arrow horizontal and negative percentages show Red arrow pointing downward.

 

[Chip Taylor]

Very nice work around Roger!

Thank you very much for taking the time to review my spreadsheet, and thanks
for reporting this to Microsoft.

Best,

ChipTay