# 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?

• If it's less than 20, won't you change the results if you make it exactly 20? – Kyralessa Jan 27 '10 at 18:41

Try this array formula:

``````=GEOMEAN(VALUE(SUBSTITUTE(A1:A6,"<","")))
``````

This formula does everything in one shot. No `IF` statements, no dragging formulas down. It loops through A1 to A6, substitutes any "<" with a blank, takes the value, then takes the geomean of the resulting values.

All you have to do is replace `A1:A6` with whatever range you want the geomean of.

Important: To use an array formula, you need to type in the formula, then press:

Ctrl-Shift-Enter

(If you just press Enter, it won't work.)

If you do this correctly, braces `{ }` will appear around your formula, like this:

``````{=GEOMEAN(VALUE(SUBSTITUTE(A1:A6,"<","")))} // don't type in the braces, they will appear automatically
``````
• Glad I could help. – devuxer Jan 27 '10 at 22:18
• Hey Dan- If if helped you should mark as answered. I've upvoted as well. – guitarthrower Jan 27 '10 at 22:49
• @guitarthrower, I think you meant @frustrated with Excel, not me (if only I had the power to mark my own answers :)), but thanks for the vote :) – devuxer Jan 27 '10 at 23:02
• There I checked marked it! – frustrated with Excel Jan 27 '10 at 23:30
• @fustrated lol not uh. – Kelbizzle Jan 28 '10 at 4:44

Use a custom cell format.

• Custom cell formats done work. And no it wont change the results, but we have to be able to see that it was detected at less than 20. – frustrated with Excel Jan 27 '10 at 18:46
• can you be more clear. What about custom cell formats doesn't work? I can see why they wouldn't, but by not being specific you're not helping find a better answer. – guitarthrower Jan 27 '10 at 22:48
• @guitarthrower, let me take a crack at answering this. Custom cell formats won't work because they only change the way data looks, not what the data actually is. So, even if you could make `<20` look like `20`, the `GEOMEAN` formula would still see it as a non-number and ignore it. – devuxer Jan 28 '10 at 0:17

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.

• How would i go about creating the formula? – frustrated with Excel Jan 27 '10 at 18:48
• Use the @IF function – Shiraz Bhaiji Jan 27 '10 at 19:17
• I know to use an IF function. But how. I never understood IF Functions..i tried a few i found online but i couldnt get them to work. – frustrated with Excel Jan 27 '10 at 19:29

Assuming Your Data is in ColumnA and starts in row 1 Numbers <20 range average out to 10, so wherever there is <20

Then the formula in column b is =IF(A1="<20",10,A1)

and drag the formula down the same number of rows as the data in column A.

Or to change the numbers in place in column A use Edit-->Replace to change all the <20 to 10