Please help:
I am looking for a conditional format formula that looks at the last digit in the cell and asks if the digit is zero then the conditional format is true and I will direct excel to color the cell.
All vaules are numbers. Cells are currently set to round to nearest hundredth.
examples
1.56 (false)
1.50 (true)
20.01 (false)
20.90 (true)
Last edited by chesspupil; 07-11-2010 at 10:57 PM.
Try using this formula in conditional formatting
=MOD(A1,0.1)=0
Audere est facere
When left to the A1 yes it turned green, but when I referenced the cell in question, nonoe of the values changed...
Thanks for trying though... if you have other suggestions I'm grateful to try them out
Is there a way to send you the file if you were so inclined?
Thanks
Last edited by chesspupil; 07-11-2010 at 10:26 PM.
Does this work?
=RIGHT(TEXT(A1,"0.00"))+0=0
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Suspecting the rounding may have caused the problem with the above formula not working I made a new collum and use TRUNC to drop the extra numbers, forcing it to see a number like 1.293 as only 1.29... even when you do a little exploration and force it to see more than 2 decimal places...
thus originally you might have had 1.293 but the new cell with the Cond format applied is only working with a 2 decimal place number
So above formula still has not met the need yet.
So you are talking about the formula I posted too?
Try posting worksheet here.. click Post Reply, click paperclip icon, browse and upload...
NBVC,
your formula worked but only on the collumn where I used the TRUNC function... so, yes, it worked, thanks
Solved!
Maybe you can modify that to a single formula like:
=RIGHT(TEXT(ROUND(A1,2),"0.00"))+0=0
or
=RIGHT(TEXT(TRUNC(A1,2),"0.00"))+0=0
Last edited by NBVC; 07-11-2010 at 10:58 PM. Reason: Mixed up formulas...
Note: I screwed up those formulas a bit ...now they should be right.
Uploaded file, the new CFs didnt work, but no big, thanks for your help!
its still solved with your original formula and a truncate... but maybe i did it wrong
I applied this:
=RIGHT(TEXT(ROUND($B$7/C12,2),"0.00"))+0=0
as per my last update and it worked.... not sure if you had noted that I left a message saying the original formulas were screwy....
Yes, you are right... I had tried your CF and didnt select a changed color so no format change was done
So I goofed and you are the EXCEL GURU........
Hats off man thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks