Conditional Formatting to Show whether the Displayed Number is Rounded

**sskgintl** · 02-20-2013, 06:44 PM

I have cells set up to display only 2 decimal places. I am trying to make the text red if the number entered has more than 2 digits after the decimal. I have tried the below formula with no luck. Any ideas?
=A1<>ROUND(A1,2)

**vlady** · 02-20-2013, 08:54 PM

the round will "round it to 2 decimal places"
try below in Conditiona lformatting ->Use formula

Formula:

Please Login or Register  to view this content.

**benishiryo** · 02-20-2013, 09:04 PM

hi sskgintl, welcome to the forum. different understanding from vlady, so here's my input.

display as in Format cells? your cells will still have more than 2 decimal places if so. it just shows you 2 decimal places. conditional formatting cant help you identify a formatting with 2 & non-2 decimal places. maybe, just maybe VBA will be able to help you in such a case.

if your cell has a formula like the ROUND you did, then it wouldn't have any problems. but then, you wouldn't need any conditional formatting.

an alternative is doing data validation, forcing users to use 2 decimal places. select the cells you want the validation applied to (say B1:B5). go to Data -> Data Validation -> Allow: Decimal -> Equal:
2