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:46This 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: CtrlShiftEnter (If you just press Enter, it won't work.) If you do this correctly, braces



Use a custom cell format. 


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. 


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 

