RIGHTcommand last digit in cell = zero?

chesspupil

Board Regular
Joined
May 21, 2006
Messages
52
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)

I thought perhaps I could do so with the LEN command or the RIGHT command or SEARCH

(Application- The timer on an X-ray machine can only select in 10th's of a minute, When I select an exposure factor (EF) (Millia-amps * time) I have a formula set up to display my milli-amp choices (also in tenths) So it takes my 30 different Milliamp choices and divides the EF to give me a time... I want the time that ends in a zero to be highlighted.

Thank you
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Try CF formula for A1 cell:
=MOD(A1*100,10)=0
 

chesspupil

Board Regular
Joined
May 21, 2006
Messages
52
=MOD(A1*100,10)=0


That didn't do the trick... I am curious I have the round function used to 2 decimal places and the cell formatted for 2 decimal places

Adding the condition actually changes the values that anther cell uses as reference.



IS there a way I can send you the excel file if you are intrested.
 

chesspupil

Board Regular
Joined
May 21, 2006
Messages
52
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.
 

chesspupil

Board Regular
Joined
May 21, 2006
Messages
52
Credit to NVBC...


=RIGHT(TEXT(A1,"0.00"))+0=0 (in the CF where Conditional format is set to Formula is


But I had to use the TRUNC function to get the CF'd column to follow along with what I needed.

Thanks,


Solved!
 

Forum statistics

Threads
1,082,285
Messages
5,364,287
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top