Working with <## data

**Xipha** · 03-03-2011, 08:51 AM

Hi, I have found lots of useful info on this forum before, and I am not a new excel user (I am self taught), but one problem keeps stumping me. I work with a lot of data that contains the < sign. The lab gives us a sheet that has the data, but if is not detected it is entered as <0.05 for example. I have come up with long, tedious ways to work around this in my formulas, but if I want to look up the minimum value (which in some cases may be <0.05) how do I look up in a range and include those numbers beginnning with <? The IF function seems to have trouble with conditions on a range of cells, and I am still learning the numerous other functions at my disposal, so there may be one I have yet to discover that will solve my problem.

E.G.

Data: 23, 45, 62, <0.05, 5, 34, <0.10, 12, Not Analyzed, 14, <0.05
Now I want to look up what the minimum value is (for this data set it should return <0.05). I may have up to 50 similar data sets in my sheet that I would be using the autofill to copy the function from the first data set into. Maybe there is no elegant solution, but I figured maybe someone here knows something I don't! And my life would be a whole ton easier!

**Xipha** · 03-03-2011, 09:03 AM

And just to clarify, not all the data sets would necissarily have data with <0.05

**Cutter** · 03-03-2011, 09:28 AM

Can you upload a small sample file?

Your first problem is that a cell showing <0.05 would be a text value and would be ignored by the MIN() function. I'm thinking you would have to use a helper column (or row) to convert any/all such text values to numerics and do your MIN() calc on that range.

**CANAPONE** · 03-03-2011, 09:29 AM

Hi,

in the example Data in A1:Z1

23, 45, 62, <0.05, 5, 34, <0.10, 12, Not Analyzed, 14, <0.05

=MIN(IF(ISNUMBER(SUBSTITUTE(A1:Z1,"<","")+0),SUBSTITUTE(A1:Z1,"<","")+0))

Array MIN to be confirmed with control+shift+enter.

Regards

**arthurbr** · 03-03-2011, 09:38 AM

Why don't you simply get rid of the "<" using Ctrl H - Replace what : < With (nothing) and click "replace all", then apply the usual formula

**NBVC** · 03-03-2011, 09:40 AM

If you want to actually return <0.05, then, assuming data is in A2:A12 try:

=INDEX(A2:A12,MATCH(TRUE,SUBSTITUTE(A2:A12,"<","")+0=MIN(IF(ISNUMBER(SUBSTITUTE(A2:A12,"<","")+0),SUBSTITUTE(A2:A12,"<","")+0)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**Xipha** · 03-04-2011, 08:09 AM

The < is very important in the final spreadsheet, so I don't just want to make it go away.

**NBVC** · 03-04-2011, 08:12 AM

Did you try my formula above? It retains the "<" symbol.

**Xipha** · 03-04-2011, 08:17 AM

Perfect! Both solutions worked the way they were supposed to (although I did have to take the fixed references out of the "INDEX" function in order to apply the formula to all the data set

), now I just have to learn what exactly each function does and the how the logic is applied, and I think my life will be a lot easier when I run into similar problems! Thanks for all the help, it's given me a starting place to learn some new stuff