Number format code that multiplies by powers of 1000

English
, , , ,
#1

I want to keep the values in the cells in plain metric units such that all formulas are the basic relations as would be found in a textbook, with no unnecessary, confusing, and mistake-prone constants in the formulas.

However, I want to display the values scaled to an easier to read order of magitude.

For division by powers of 1000, I can use commas, so I can use the format codes:

0.###,,,"G"
0.###,,"M"
0.###,"k"
0.###

Or I could even construct something more generic like this:

[>=1000000]0.###,,"M";[>=1000]0.###,"k";0.###

(There seems to be a bug that prevents me from using more than 3 conditions, so for now G is omitted.)

But for m, μ, n, etc., I cannot find a way.

I found this clever looking hack but the percents don’t seem to work properly:

Number format code to display x significant figures
Decimal numbers
#2

Could you possibly give explicit examples here using just characters available for answers of what you want to achieve and what you get or not get?

#3

@ROSt53: For example, I want to format 0.00001234567 as 12.346μ.

#4

@James1: What I would do is using the link of chandoo and experiment. I don’t have enough time to do it myself but I would give it try. It seems very feasilble. % creates mutlipes of 10.

#5

@James1: could stop to make a test % and , behave in LibO differently. Thus for me the question should be what are the multiplier and divider symbols in Calc. Would raise question in users@global.libreoffice.org

#6

@ROSt53: Did you find anything? I don’t have the proprietary alternative to actually try the test you suggest.

#7

@James1: What do you mean with “proprietary alternative”? Microsoft Office/EXCEL? I don’t have it on my PC anymore. Rely fully on LibO
.
What do you mean with “trainling point”?

#8

@James:made a test: entered 1 and formated cell with 0.### and 1 appeared as 1.
I don’t understand what you mean with the bug mentioned above.

#9

@ROSt53: Sorry - as I was correcting myself this website went offline. If 1000 is formatted with 0.###,"k" it is displayed as ‘1.k’ rather than ‘1k’. Also try formatting an integer with 0.###"text"; you should get something like ‘1.text’ rather than ‘1text’.

#10

@ROSt53: Btw, % multiplies by 100, not 10. According to the chandoo article, multiplying by 10 is achieved by 2 percents and a comma, since x *100 *100 /1000 = x *10. And dividing by 10 can be done with a percent and a comma, since x *100 /1000 = x /10.

#11

@ROSt53: And yes, I meant m$'s spreadsheet program, which I got rid of years ago and switched to OOo, long before Oracle bought Sun and LibO had forked off OOo.

#12

I removed the line beginning “Ideally…” because m$ compatibility is no longer required since the person I’m working with on a project has installed LibO. I also think some of the issues with LibO come from copying issues and quirks in m$ office for best compatibility - I’m a bit resentful of this.

#13

@James1,

0.### format on a 1 shows correctly the result 1. Reason ### displays a maximum of 3 digits after the decimal point. Try to enter 1, 1.1, 1.11, 1.111, 1.11111 and you will see.

It is important to understand the 0 and #. I would neglect this first and work only with a 0. Your major problem is to adjust the display of a value smaller than 1 as as figure larger then 1 and not having the % visible.

I did a row of tests and found that

  • Calc does not accept the <crt j> or <crtlj> as a format. I don’t know why.
  • “k” is not need just k is enough
  • comma and % work in the way as indicated in the link you provided when you raised your question

Problem I could not solve was the % shows up.
Possible solution

  • ask a new question on how the % sign can be made disappearing in Calc or if there is another multiplier by 100 which does not show up like the comma for a division by 1000
  • read through all the additional links given in the link you provided at the beginning

If you would like to have my test sheet, let me know.

Please understand that I cannot do more testing and searching for you. I feel I brought you on the track, good luck.

#14

I do understand 0 and #. As I explained in a previous comment, the site crashed when I was trying to correct myself. Grrr! When the site came back online, I was no longer able to edit the comment. To avoid further confusion, I’ve just deleted the comment.

#15

However, I do not understand what you mean by “<crt j="">”. The double quotes around k may indeed not be necessary in this case, but without knowing with certainty every single format code character, it is a good idea to quote any text characters just in case they get interpreted as format code.

#16

If you could attach your test spreadsheet to this answer, that would be great, thanks.

#17

sorry but this is rubbish. I don’t know what happened, it should be ( a space between ctrl and j.
I don’t see a possibility to attach a file here. Ask your question in users@global.libreoffice.org. I see a chance to mail you the file there

#18

Additional, could you possibly create an example showing

  • the data as you get them
  • as you want to enter them
  • as you want to display them
    and explain why you want to have it that way.
    Reason: I MIGHT have a kind of workaround if the % problem cannot be solved.
#19

Hi @JamesHaigh,

Did you ever find a solution to your formatting question?

If you think that changes to the LibreOffice code could help you implement your desired formatting, please file an enhancement bug and provide as much information about this new feature as possible. Don’t forget to mark your bug as an ‘enhancement’. The QA team will be happy to help you triage your feature request in the bugtracker.

Please post a link to any bugs you file in a comment below using the format “fdo#123456”.

Thanks!

#20

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.