How to graph data that have '<' or '>' values?

[cclint]

I work in a microbiology laboratory and report data generated in the lab based on a multiplier or sorts. So for example, if I don't recover any bacteria on any of our agar plates, I can't report 0 as a value, I must report <10. This is due to preparatory dilution steps. My problem is that I want to trend the data, which includes determining statistical validity of the data.

Is anyone aware of a way to graph values that include a less than symbol?

Or, alternately, if I know the conversion applied to the original data to modify it to the reported result, can I tell excel to treat the values as different values?

For example, in the attached spreadsheet, you'll see the Results column of the TAMC test. All of the results listed as <10 are actually 0, 10 = 1, 15 = 1.5, 20 = 2, 25 = 2.5 and so on. How could I tell excel to use the secondary values to perform the calculations with?

Any suggestions?

 

[Chihiro]

Easiest method is to use helper column and use that column to plot chart.

Insert column beside F with header like "Convert" or some such.
In G3:
=IF(LEFT(F3,1)="<",0,F3/10)

Copy down.

 

[Hui]

I'd suggest using a technique like this


Instead of entering <10 I'd suggest using a very low value like 0.0001
Then apply a Custom Number format to the Results columns above like:
[<0.001]"<10";[<1]0.00;0

This will show any values of 0.0001 as <10
and values < 1 as 0.00 format

knowing this we can use formulas to get the min, max , average etc