Excel - Conditional Formatting Query

Cotta

Hi All,

I have a "conditional formatting" function within a number of cells so that anything => 70% but <75% is returned as an amber. All is good thus far, however one of the cells in question has 70% in it but is returning red, the trouble in the cells value is 69.9% but has been automatically rounded up to 70%, is there anyway to correct this?

Thanks

Stompa

A quick and dirty way would be to use >=69.5% and <74.5%, though it would break if you were to change the number of decimal places displayed in the cell.

Cotta

Stompa wrote: »

A quick and dirty way would be to use >=69.5% and <74.5%, though it would break if you were to change the number of decimal places displayed in the cell.

That will probably be the best option, thanks.

maas

Stompa wrote: »

A quick and dirty way would be to use >=69.5% and <74.5%, though it would break if you were to change the number of decimal places displayed in the cell.

Should that be;

>=69.5% and <75.5%

Also I dont think the conditional formatting works on what the cell looks like (i.e its format) but the underlying value. So regardless of how you format the cell it should show the same color determined by the value (in this case 0.699).

Stompa

maas wrote: »

Should that be;

>=69.5% and <75.5%

No, I don't think so, the original requirement was <75%. Rounding will cause values >=74.5% to display as >=75%, so you need to use <74.5%.

maas wrote: »

Also I dont think the conditional formatting works on what the cell looks like (i.e its format) but the underlying value. So regardless of how you format the cell it should show the same color determined by the value (in this case 0.699).

If I've understood correctly, I think the issue is that the OP wants the colouring to depend on the displayed value rather than the actual underlying value.

Finknottle

I think the OP wants 69.9% to display as 70% and to qualify for amber colour which is for values >=70%.

If you are able to hold the underlying value in Excel rounded to 2 decimals, (i.e. =ROUND(InsertYourNumberHere,2) then your conditional formatting will work regardless of the number of decimals you display.

However, you need to keep in mind that Excel would now see your original 69.9% as 70.0% in any subsequent calculations.