Conditionally format my rows if ends with

[serraostudent1]

just got upgraded from 2003 to 2010! I have about 500 rows of data. In my amount column I need immediately identify the rows that end with .0 and .00. do I have to set up two conditions? If so how?

 

[JoeMo]

My results vary with this! None of the formulas catches the .80 or .20 cents the first INTformula catches the integers ending with 00.

The first formula should catch whole numbers ending in 00 like 100 or 1000, etc. they are after all whole numbers. Post some sample data and the exact CF formula you are using, we can't see what you see w/o the workbook in front of us.

 

[serraostudent1]

1026.00 formula =int(a2)=a2 " true" correct same formula 3178.93 "false" also correct. I can use this formula for the first color. Here's my problem 957.90, 2096.10, 1220.20 are not being seen as "true". I do understand they are not whole numbers but I don't know how to catch them. Thanks for working with me on this.

 

[JoeMo]

1026.00 formula =int(a2)=a2 " true" correct same formula 3178.93 "false" also correct. I can use this formula for the first color. Here's my problem 957.90, 2096.10, 1220.20 are not being seen as "true". I do understand they are not whole numbers but I don't know how to catch them. Thanks for working with me on this.

Use the second formula I gave you in my initial post for decimal numbers.

 

[serraostudent1]

Ok! The second formula e.g. INT(a2)<>a2 catches all decimals (I.e. 147.56, 1111.85, 356.10) how can I get it to ignore the .56 and .85 decimals and only catch the .10, .50, decimals ending in zero?

 

[JoeMo]

Ok! The second formula e.g. INT(a2)<>a2 catches all decimals (I.e. 147.56, 1111.85, 356.10) how can I get it to ignore the .56 and .85 decimals and only catch the .10, .50, decimals ending in zero?

Are you asking for a condition that ignores decimal numbers that have more than one significant digit after the decimal place?

If so, try
=ROUND(A1,1) = Round(A1,2)

 

[serraostudent1]

hurray! You are a MASTER! It works for both! Had I known the zero's were insignificant I would have asked correctly the first time! That formula has just save me from countless hours of manual labor! Don't forget what you know! I and many others will need you! Thank you Joe! For the formula and for not giving up on me!

 

[JoeMo]

hurray! You are a MASTER! It works for both! Had I known the zero's were insignificant I would have asked correctly the first time! That formula has just save me from countless hours of manual labor! Don't forget what you know! I and many others will need you! Thank you Joe! For the formula and for not giving up on me!

You are welcome - thanks for the reply.