Prev: FYI: Microsoft admits Office patch gaffes
Next: ADO - Access to Excel
From: Sherm H. on 12 Mar 2010 19:35
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?
From: OssieMac on 12 Mar 2010 22:50
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?
From: OssieMac on 12 Mar 2010 22:58
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


 | 
Pages: 1
Prev: FYI: Microsoft admits Office patch gaffes
Next: ADO - Access to Excel