How to get excel to recognize <20 as the number 20 without removing the "<"?

How to get excel to recognize <20 as the number 20 without removing the "<"? "I have mass amounts of lab reports with <20 mg/L results. I am trying to take the Geomean of a column of numbers EX. <20, <20, <20, 20, 1400, 2500, <20, <20. I noticed when i take the ""<"" away the geomean changes. How do i trick excel into thinking its a real number?"
Asked by: Guest \| Views: 330

Total answers/comments: 1

Comments display order:

bert [Entry]

"You need to create a formual in another column that either reads the value directly or takes the value part if there is a ""<"".

Adding a 0 or custom cell format will change the Geomean.

EDIT

Assume you have ""<20"" in A1

In B1 write: =MID(A1;1;1)
In C1 write: =IF(B1=""<"";20;A1)

The answer in C1 should now be 20. If you change A1 to 5 then C1 should change to 5."