Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

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

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

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
add comment

4 Answers 4

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
share|improve this answer
    
THANK YOU SO MUCH!!!! IT TOTALLY WORKED!!! –  frustrated with Excel Jan 27 '10 at 21:35
    
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
show 1 more comment

Use a custom cell format.

share|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. –  devuxer Jan 28 '10 at 0:17
add comment

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.

share|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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.