Percentages & conditional format

[rbobcat1]

a b c. D
1. 423 447 95% 94.63%
2. 244 245 100% 99.59%
3. 179 179 100% 100.00%
As you can see above 2C is rouned up.
If I set cond fmt to set the color to green for those at 100% it only works for 3c not 2c.
And if I use the concatenate (see attached) it does work as expected either.

 

[Nebu]

Hi:

Try this in your CF

=ROUNDUP(C2,2)=100%

Thanks

 

[Bhawani]

Please find attached if it is same you required.

 

[rbobcat1]

Thx, Bhawani I cannot open the file u uploaded

 

[Khalid NGO]

Hi,

See if this is okay.

Regards,

 

[rbobcat1]

Khilid, Thx
The problem is 2c is not truly 100% and I dont want it to reflect that.
I prefer it to show 99%. It only shows that if i move the decimal point to the left.

 

[NARAYANK991]

Hi ,

I think the issue is unnecessarily being confused.

Which of the following are you interested in :

1. If the value in column A is equal to the value in column B , only then the cell should be colored GREEN

2. If the value in column A is close enough to the value in column B , then too the cell should be colored GREEN

If it is the first , then why bother about percentages and rounding up ? Just compare the two values.

Narayan

 

[rbobcat1]

Yes and no,
If the value is 100 then green
if the value is 99.9 or less then a different color, say yellow.

the problem is excel rounds up when moving the decimal to the left.

column c is no decimal point
column d is deceimal moved to the right 2
therefore c3 is not truly 100% but 99.5% and should not be green.

I dont want the .?? showing but maybe i have no choice.

so yes 244/245 = 99.59% not 100%

 

[Nebu]

Hi:

If you can manually highlight the colors you want in each cell covering all the scenarios I can try doing it.

Thanks

 

[rbobcat1]

Hi:

If you can manually highlight the colors you want in each cell covering all the scenarios I can try doing it.

Thanks

I have attached a better explanation

 

[Nebu]

Hi:

I have applied conditional formatting to column C & D

Rules are as below:

1. Red=ROUNDDOWN(D3,2)<=0.79 anything lesser than or equal to 79% the round down basically round down the the percentages to the lowest available whole number eg: 78.75% will be round down to 78%.
2. Yellow=AND(ROUNDDOWN(D3,2)>0.79,ROUNDDOWN(D3,2)<=0.99) anything higher than 79% but less than or equal to 99%.
   
3. Green=ROUNDDOWN(D3,2)>99% anything above 99%.

Thanks

 

[Khalid NGO]

Hi,
I have used some helper columns, see if it is okay:
I found a typo in your H3
Regards,