Generating graphs with less than results

[Scott from Gippsland]

I currently export data from a laboratory into excel, and need to generate a
series of graphs based on the data.

Due to the nature of the tests done by the laboratory, certain results are
presented as less than (<00.00).

Is there a simple way of generating graphs when parts of the data have the <
symbol in front of the number?

Hope someone has some ideas

 

[Jon Peltier]

Scott -

Excel really needs numbers to put into the plot. Does <00.00 indicate a low
value (like zero), or does it indicate a missing value? Set up an extra
column with formulas like this:

=IF(LEFT(B2,1)="<",[value],B2)

where [value] could be zero, if that's what it means, or NA() in a line or
XY chart to skip that point (a line is interpolated across the missing
value), or zero in a column chart. Using "" gives unexpected results,
because it does not produce a blank cell (even though it looks blank) but
rather a cell with a short text string, which Excel values as zero. The NA()
produces an #N/A error in the cell, but makes the chart work; use
conditional formatting to hide the error.

- Jon