using less than sign gets rid of decimals

  • Thread starter Thread starter Geodave
  • Start date Start date
G

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.
 
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
 
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.
Click to expand...

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
 
Ron Rosenfeld said:
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
Click to expand...

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 said:
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
Click to expand...

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

[<=10]"< "#.00;0.00
Click to expand...

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
 
Back
Top