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?
migrated from stackoverflow.com Jan 28 '10 at 1:46
This question came from our site for professional and enthusiast programmers.
Try this array formula:
This formula does everything in one shot. No
All you have to do is replace
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
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.
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