Re: [libreoffice-users] can't prevent Calc from modifying the number I type into a column

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: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy