up vote 3 down vote favorite
share [g+] share [fb]

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?

link|improve this question
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
feedback

migrated from stackoverflow.com Jan 28 '10 at 1:46

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 3 down vote

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
link|improve this answer
THANK YOU SO MUCH!!!! IT TOTALLY WORKED!!! – frustrated with Excel Jan 27 '10 at 21:35
Glad I could help. – DanM 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 :) – DanM Jan 27 '10 at 23:02
There I checked marked it! – frustrated with Excel Jan 27 '10 at 23:30
show 1 more comment
feedback
up vote 1 down vote

Use a custom cell format.

link|improve this answer
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. – DanM Jan 28 '10 at 0:17
feedback
up vote 1 down vote

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.

link|improve this answer
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
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard