Excel 2007 : How to make Excel treat "less than numbers (<1)" as "numbers (1)"?

**lisay** · 12-22-2010, 08:17 PM

Hello,

I have a column of lab data with mixed numbers (e.g. 10, 100, 1000) and "<1".

I'm trying to use conditional formatting to highlight those cells that are greater than 100. However, in doing so, the "<1" cells were also highlighted. It seems that Excel is treating <1 as text and as greater than any numbers.

I want the Excel to treat "<1" as "1", so that it obeys the conditional formatting rules and won't get highlighted.

I also want to do calcuation (+ or -) on those <1 cells taking it as a real 1. Now it gives a "#VALUE!" when <1 is involved in any calculation.

Anyone knows how to make Excel treat "less than numbers (<1)" as "numbers (1)"?

Your help is greatly appreciated!!

THANKS Jeff! The AND logic in the conditional formating formula worked!

**jeffreybrown** · 12-22-2010, 08:21 PM

So we can get you the best answer, can you upload a small sample workbook with what you have and what you expect? This will eliminate any possible confusion or guess work.

**shg** · 12-22-2010, 08:52 PM

You could use find and replace to get rid of the "<", which would convert those cells to numbers.

**jeffreybrown** · 12-22-2010, 09:04 PM

How about...

=AND(ISNUMBER(A2),A2>$B$1)

**lisay** · 12-22-2010, 09:11 PM

I'd like to keep the cell content as it is, and show <1 not 1 when it displays. So the find/replace is probably not desired. ..

Is it possible to have Excel think "< number" as the "number" without <, during conditional formating? I've attached a small workbook and hope it explains. . Trying to get the Excel not to pick out '<1' when the conditional formatting logic says "highlight those numbers greater than 100”.

**lisay** · 12-22-2010, 09:23 PM

the AND worked. Thanks Jeff!

**TelexMetals** · 02-07-2020, 10:59 AM

Is there a way to convert "<10" to a number format to show just 10?

**Pepe Le Mokko** · 02-07-2020, 11:00 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread