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?

  • 2
    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:


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:


(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
  • 1
    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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy