Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index

At 00:36 12/09/2018 -0400, Art Noname wrote:
I have a column of numbers I want to enter into a calc column.

That may be your first mistake: these may not be numbers. Read on.

One of the numbers is 127.700 (for instance). So, I copy the number and paste it into the desired column. But, when I go back and check the contents of the column, it reads 127.7 instead!!!

Your punctuation suggests that you are surprised, but you should not be: that is how spreadsheets work. For numbers, the internally stored value has as much precision as is available in the implementation. How the number is *displayed* is a separate issue, and the default will be two fractional places. Only one is shown here because the second fractional place happens to be zero.

And, if I search the spreadsheet for 127.700, there are no hits!!!!

It's difficult to search for floating-point numbers, or even know what is the point of doing so, as calculations will generate rounding errors, and you will not be able to rely on any particular level of precision. You will probably say that you don't want to perform calculations with these values - which would be Good News. Read on.

I tried formatting the column to show 3 significant numbers after the decimal point, which looks good....but when I check the actual contents of that cell, it is still 127.7!!!

Actually, the contents will effectively be 127.7 followed by as many zeroes as can be stored in the internal representation. And I hope it will not be a revelation to you to learn that 127.700 and 127.7 are actually equal. If necessary, ask yourself which is larger. Also, in any calculation (carried out to the greatest precision allowed by the internal representation) these values would give identical results. So *as numbers* they are equivalent.

At 02:44 12/09/2018 -0400, Art wrote:
I'm listing frequencies in Megahertz, so the zeros to the right of the decimal point are significant.

That is very true, of course, for representing such a physical quantity. But it is your business to keep track of levels of precision, not the spreadsheet's.

It seems like calc has some sort of deliberate action to inhibit entering of trailing zero's (after the decimal point).

You can enter them, but yes: what you then see in the cell is controlled mostly by the cell formatting.

How do I turn off this "feature"???

By reconsidering the design of your spreadsheet. Read on.

When I copy and paste 117.000 into a cell, I expect to see 117.000 when I examine the contents of the cell.

Then don't use numbers in a spreadsheet.

I actually don't have a problem with how the number is displayed in the spreadsheet, but when I search for the number I entered into the column, I need to get hits on 117.000 when I search for 117.000.

So you wish to search for items exactly as you have entered them? And I'm guessing that you will not be doing any calculations with these frequency values: you will not be subtracting one from another to work out how many channels they are separated by, and not averaging a collection of such values? Is that the case?

If so, what you have here and need to deal with are actually pieces of text that happen to look like numbers. All you have to do is to format relevant cells (probably columns) as text *before* you enter your values. Problem solved! (You will notice that text values are left-aligned by default, unlike numbers.)

If you do need later to perform calculations on these values, you can convert them on the fly to numerical values using the VALUE() function in your formulae. In some contexts, conversion even happens automatically, without the need for that.

One word of caution: if you enter values in text-formatted cells by typing, you will get what you need. But you talked of pasting values. If you paste numerically formatted values from another spreadsheet cell into a text-formatted cell, this may change the format of the cell - which you won't want. To avoid that, use Paste Special... instead of ordinary Paste and remove the tick from Formats.

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.