[Solved] Truncating to Last Non-Zero Digit

[Chade]

I'd like to truncate without rounding to the last non-zero digit.

If the cell contains a percent such as 0.0485, I'd like to display it as 4.85%, but if the value is 0.04, I'd like to display it as 4%. If I use the percent format codes, I can only get one of the two to look correct in the same cell.

This seemingly easy task has got me stumped!

Thank you!

[FJCC]

Try this format code

Code: Select all

0.####%

It shows the decimal place even for an integer and I don't know how to prevent that.

Edit: Never mind. I realized you want to change the value of the cell, not just the display.

[Lupp]

Are you aware of the implications?

If a number not is integer it will generally (with very few exceptions) not be represented in a computer with its exact value. Most likely you assume 1/50 = 2% to be represented with exacly this value, but that's impossible in a strictly dyadic system as actually used. In short: The "last digit not being 0" does, as a rule, either not exist at all, or can, at least, not be identified based on the information the computer has. Rounding is indispensable concerning task of the kind. In addition cases where you can know that 2% is exact, are very rare in real life.

Therefore it is good practice to communicate (e.g. display) numbers together with a clear information about the assured precision. The additional information is mostly given in the way that the actually shown digits are trustworthy as significant in the sense (at least) that the true value cannot differ by more than one unit (plus/minus) of the least place value in decimal format (LSD, "least significant digit").

And for the same reasons it is good practice to not suppress trailing zeros if significant. Doing otherwise might mislead people to estimate a given precision by far to low.

Ok. That's math. But math is just another name for good practice in the context.

If you insist in bad practice you may use what you are already told. Don't miss in this case to also consider in what way you can suppress the zero in 40%.

(Editing:)

It shows the decimal place even for an integer and I don't know how to prevent that.

LibreOffice since V5.2 does suppress the decimal separator if no further digit is displayed. I am not happy about that. You won't be surprised if you read my post.

[acknak]

I don't think it's possible with just a format code, but you can always select different format codes if you can use a separate column to display the values:
=IF(LEN(TEXT(A1;"General"))<6;TEXT(A1;"0%");TEXT(A1;"0.00%"))

[Chade]

Thank you for taking the time for very well-reasoned replies.

I am generally aware of the mathematical implications of truncating on computer logic, but not to the specificity that you've shared with us. I did, however, miss the 10% being reduced to 1% problem, so nice catch there.

In this case, those mathematical implications aren't an issue. At the risk of being too verbose, let me explain my situation in a bit more detail:

I am using Calc to recreate a sizable amount of government and internal forms relating to banking. I am using an 'input' sheet for bank officers to fill in values which are in turn read by a 'reference' sheet which analyzes/manipulates those values. The individual forms are completed with the resulting data from the reference sheet. The idea is that the computer illiterate officer fills out the input sheet, Calc populates the whole mass of forms, and then the officer prints and moves on with life. Since we currently fill this stuff in with typewriters, each form individually, you can easily guess the significant impact on accuracy and speed.

Specifically, I'm trying to populate forms with an interest rate (raw value contained in the reference sheet). Personally, I'd love to just set it to 0.000% and call it a day. Unfortunately, government guidance regarding these forms mandates that if the rate is 0.04, it must be displayed as 4%. If the rate is .045, it must be displayed as 4.5% on the form, and so on. Since I cannot predict what the interest rate will be (it may be 4, 4.5, 4.85, etc.), I need to perform some logic on the form side. For the form's purposes, the percent rate could be text, since the forms are strictly end-user facing output. All calculations take place within the reference sheet (this is why I'm not concerned about the mathematical implications of truncating, since it is the final output. Thank you again @Lupp for the excellent and important information regarding this.).

The solution requires functions/format code or some other form of logic, but that is where I'm stuck. Function work is fine, of course, not just format codes.

Following acknak's advice, perhaps I could somehow count the number of digits trailing the zero in the input, then set up a series of styles to trigger on differing lengths?

Edit: trailing the DECIMAL, not zero!

[acknak]

Following acknak's advice, perhaps I could somehow count the number of digits trailing the zero in the input, then set up a series of styles to trigger on differing lengths?

I may have missed something; I'm not sure I follow why you want to look for the zero. You can do that, but it's easier to just look at the length. The General format won't include any non-significant digits after the decimal, and as others have pointed out, truncating may be risky.

Using styles (possibly through a conditional format with STYLE) to provide different number formats is a good idea and avoids the need for another cell. Worth looking into.

[Lupp]

I feel a bit uncomfortable now, and this must be due to my poor English.

If the interest rate (kind of) can be any value whether returned by a formula or directly entered, there simply is no reasonable solution because the fractional part my have 15 digits of which the last one is different from "0". That's not the way to give rates anywhere in the world I think, whether in percent format nor otherwise.
There must first be a rounding. No body can suggest "...some other form of logic..." if not a specification is given.

Now let's suppose we are still talking of Formats. The rate may be rounded in advance to the, say, 6th decimal, that's a least significant digit on the place of (1/10000)%. The "virtual digits" behind that place are assumed to be "0". An extreme resolution I suppose.

A value may now be displayed based on the format code "0.####%". That's ok? In recent LibO it will also suppress the decimal separator (point here) in lack of decimal places. In AOO it will not.
It must be AOO? No problem: The TEXT() function will accept the same code, and return what we want, except one point too much in some cases.

Code: Select all

=SUBSTITUTE(TEXT(A1;"0.####%");".%";"%")

will fix that. Including the rounding we get

Code: Select all

=SUBSTITUTE(TEXT(Round(A1;6);"0.####%");".%";"%")

and if the result is needed later for numeric calculations we may apply the VALUE() function to it.

Where did I miss something substantial?

The attached demo was created wit AOO 4.1.3. In Version 5.2 or higher of LibO the SUBSTITUE() level of the relevant formula is not needed.
The TEXT function as applied takes for sure that the language / locale setting for the respective cells uses the point as the decimal separator.
To work also under comma locales the formula should be

Code: Select all

=SUBSTITUTE(TEXT(ROUND(A1;6);"0"&MID(1/2;2;1)&"######%");MID(1/2;2;1)&"%";"%")

Ok. That's a bit complicated because I don't know a really simple way to get the current decimal separator for use in a formula. MID(1/2;2;1) is the simplest expression I found.

[Chade]

I'm sorry! I made a mistake in my prior post...it should have read "trailing the DECIMAL", not "trailing the zero". Quite a serious error there! I will edit it shortly for future readers.

By "some other form of logic", I meant start thinking about the rate as a text value or focus on the length, rather than solve it through eliminating zeroes.

Re: rounding/fractional rates: I think the issue may be confused a bit, so I will try again:

My input will be something similar to:
##.###%
such as 4.05, 4, 4.85, 14, 14.5, etc.

My condition is:
The smallest number of known digits must be displayed.

So:
If 4.05, I must display 4.05%, not 4.050 or 4%.
If 4, I must display 4%, not 4.000%
If 4.875, I must display 4.875%, not 5%.

I want to do this in the same cell. The length of the input is variable, so I can't use a format code (will either round OR produce trailing zeroes).

[Lupp]

The length of the input is variable, so I can't use a format code (will either round OR produce trailing zeroes).

This is not correct. As others and also myself already showed, a format code like "0.######%" will not fill up the maximum number of decimal places (6 in the example) with trailiung zeros. That's the difference between "#" and "0" used in the code. The only problem in current AOO ist the orpahned point in case of no decimal places at all. (LibreOffice V5.2 or higher avoid this.)

If you want to format in situ you have no choice other than to apply a format code. Even if you resort to user code ("macros") there is no reasonable way to directly define the appearance of a cell containing a still numeric entry or result.

If you accept to change the 'ResultType' to 'Text' by that, you can assign whatever 'String' you want by a user Sub. Regarding some restrictions the text can then again be accepted (with a correct result) by the VALUE() function if further calculations are needed.

To do this for direct entries is possible handling the Sheet event 'Content changed'. For calculated values it's more complcated.

[Chade]

Switching to LibreOffice and using the ## format code instead of 00 solved my issue. Thank you all again!