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?

 

[bschwartz]

I'm afraid I don't really understand your question.

.0 and .00 are the same integer, and that's all they are. If you see decimals with zeroes it's only because of formatting.

Do you want to highlight all whole numbers?

 

[serraostudent1]

I'm sorry! I should have given a better example! (e.g. 30 or 300) didn't mean to put the decimals. And thank you for not insulting me, I do realize that .0 and .00 are the same. Alter question, what ever solution you come up with will it also work if there are cents!(e.g. 1234.50, 1200, or 3450.20)?

 

[bschwartz]

That depends. What do you want to happen if there are cents? For example, should 3450.20 be highlighted or not?

Also, can your conditional formatting rule be summed up with the following statement? "Identify all values that are evenly divisible by 10."

If so, you can use this formula in the conditional formatting rule: =MOD(A2,10)=0

 

[JoeMo]

Or, if you want to separate values that are whole numbers from decimal numbers (highlight one or the other), a CF formula like this will do it:

=INT(A2)=A2 <-- Whole numbers

or
=INT(A2) <>A2 <--- decimal numbers

 

[serraostudent1]

Yes, it should be highlighted. I will try this formula and let you know how well it works for this project! Thanks!

 

[serraostudent1]

Thanks Joe for this option! It would be cool if one could be blue and one a different color! Can these two work together?

 

[JoeMo]

Thanks Joe for this option! It would be cool if one could be blue and one a different color! Can these two work together?

Sure, just enter each as a condition (one at a time) for the same set of cells and choose a different color for each condition.

 

[serraostudent1]

I tried this first in the sheet! It gave me nothing. I removed the =0 it gave me "the result of the cell value divided by 10. Tried to wrap it in an if function : if(mod(a2,10)"0",0) received bad news need help please! Beginner level!

Tanks

 

[serraostudent1]

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