Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
Hi Chip
Works fine for me. Mark the cells againConditional FormattingManage RulesEdit 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
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" wrote: Hi Chip Works fine for me. Mark the cells againConditional FormattingManage RulesEdit 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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" wrote: Hi Chip Works fine for me. Mark the cells againConditional FormattingManage RulesEdit 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
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. -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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" wrote: Hi Chip Works fine for me. Mark the cells againConditional FormattingManage RulesEdit 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting problem in Excel 12 / 2007
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 "Roger Govier" wrote: 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. -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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" wrote: Hi Chip Works fine for me. Mark the cells againConditional FormattingManage RulesEdit 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. -- Regards Roger Govier "Chip Taylor" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Icond Set (conditional Formatting) of Excel 2007 | Excel Discussion (Misc queries) | |||
Conditional formatting in Excel 2007 | Excel Discussion (Misc queries) | |||
Saving Conditional Formatting in Excel 2007 | Excel Discussion (Misc queries) | |||
Conditional Formatting: Excel 2003 vs. 2007 | Excel Worksheet Functions | |||
Conditional Formatting in Excel 2007 | Excel Discussion (Misc queries) |