using less than sign gets rid of decimals

[Geodave]

I am creating a spreadsheet of laboratory results and many of the values are
less that detection limits, while other values require decimals. Cells in a
column may be as follows:
< 10.00
5.62

I type in all these values, then format cells to "number" and "2 decimals".
The result is;
< 10
5.62

For consistency, I want to retain the decimals. How do I do that formating?
Thanks.

 

[Sandy Mann]

You could Custom format the cells where you want the "<" sign as:

"<" #.00

the format can then be pasted using the Format Painter icon

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Rosenfeld]

I am creating a spreadsheet of laboratory results and many of the values are
less that detection limits, while other values require decimals. Cells in a
column may be as follows:
< 10.00
5.62

I type in all these values, then format cells to "number" and "2 decimals".
The result is;
< 10
5.62

For consistency, I want to retain the decimals. How do I do that formating?
Thanks.

You could custom format the cells.

If you type in < 10.00, it should display as that since it is TEXT.

However, if you want it to display that way if you type in a 10, or a number
less than 10, try this:

Format/Cells/Number/Custom Type: [<=10]"<10.00";0.00


--ron

 

[Sandy Mann]

However, if you want it to display that way if you type in a 10, or a
number
less than 10, try this:

Format/Cells/Number/Custom Type: [<=10]"<10.00";0.00

Or if the OP wants to use the number as a number then perhaps:

[<=10]"< "#.00;0.00

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Rosenfeld]

However, if you want it to display that way if you type in a 10, or a
number
less than 10, try this:

Format/Cells/Number/Custom Type: [<=10]"<10.00";0.00

Or if the OP wants to use the number as a number then perhaps:

[<=10]"< "#.00;0.00

He could still use his entry as a number with the formatting I recommended. It
just wouldn't be the same number being displayed.

And that is an option I considered.

But from the examples he posted, and the fact that these refer to laboratory
values, I suspect that the formatting for each cell will be different,
depending on the actual lab test being run.

It would look odd, and be incorrect in some sense of the word, to display a
result of <5.00 when the test is not capable of differentiating anything with a
value <10.00.

I would probably enter either a value equal to the minimum detectable value, or
a zero, but still have the display show < 'minimum detectable amount'
--ron