Conditional Formatting of Percentages
I have a spreadsheet with 12 rows (one for each month), and 3 columns:
Incidents Closed, Closed on Time, and Percentage Closed on Time. The data in the % Closed on Time is formula (=IF(E15=0,"",E16/E15). I'm trying to use Conditional Formatting Icon Sets, Traffic Signals as follows: 90% = Green, 80-89% =Yellow, and Below 80% = Red. I can't get the Icon Sets to work correctly, getting some percentages with the incorrect color, as a new month'e data is acced to the sheet. I have the Rules set up as indicated above, and using the percent Type in the Rules. If any of the percentage values change, the colors then get all screwed up. What am I doing wrong? |
Conditional Formatting of Percentages
Hi,
You can't use percentages as such in the formulas. You need to use the decimal equivalent in the formulas. 90% = 0.9, 89% = 0.89, 80% = 0.8 Conditional format formulas. Use them in the order shown. Replace E16 with the cell that you are testing for the value. Hi, You can't use percentages as such in the formulas. You need to use the decimal equivalent in the formulas. 90% = 0.9, 89% = 0.89, 80% = 0.8 Conditional format formulas. Use them in the order shown. Replace E16 with the cell that you are testing for the value. Green. (I have assumed you mean = 90%; not just 90%) =E16=0.9 for Orange (I have assumed you mean = 80%; not just 80%) =E16=0.8 For Red =E16<0.8 Green. (I have assumed you mean = 90%; not just 90%) =E16=0.9 for Orange (I have assumed you mean = 80%; not just 80%) =E16=0.8 For Red =E16<0.8 -- Regards, OssieMac "Sherm H." wrote: I have a spreadsheet with 12 rows (one for each month), and 3 columns: Incidents Closed, Closed on Time, and Percentage Closed on Time. The data in the % Closed on Time is formula (=IF(E15=0,"",E16/E15). I'm trying to use Conditional Formatting Icon Sets, Traffic Signals as follows: 90% = Green, 80-89% =Yellow, and Below 80% = Red. I can't get the Icon Sets to work correctly, getting some percentages with the incorrect color, as a new month'e data is acced to the sheet. I have the Rules set up as indicated above, and using the percent Type in the Rules. If any of the percentage values change, the colors then get all screwed up. What am I doing wrong? |
Conditional Formatting of Percentages
Had problems with previous post and now it appears to be corrupted so I'll
try again. You can't use percentages as such in the formulas. You need to use the decimal equivalent in the formulas. 90% = 0.9, 89% = 0.89, 80% = 0.8 Conditional format formulas. Use them in the order shown. Replace E16 with the cell that you are testing for the value. Green. (I have assumed you mean = 90%; not just 90%) =E16=0.9 for Orange (I have assumed you mean = 80%; not just 80%) =E16=0.8 For Red =E16<0.8 -- Regards, OssieMac |
All times are GMT +1. The time now is 10:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com