Number format code that multiplies by powers of 1000

Question

asked 2012-10-30 00:07:07 +0200

James Haigh
36 ● 1 ● 1 ● 6

updated 2012-11-08 14:16:42 +0200

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:

http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/

Accepted Answer

answered 2013-03-01 05:42:43 +0200

qubit

5709 ● 3 ● 48 ● 41

Hi @James Haigh,

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!

Answer 2

answered 2012-11-09 02:12:03 +0200

ROSt52

1841 ● 30 ● 30

updated 2013-03-14 01:58:09 +0200

qubit

5709 ● 3 ● 48 ● 41

@James,

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.

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.
James Haigh ( 2012-11-09 13:10:14 +0200 )edit
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.
James Haigh ( 2012-11-09 13:18:51 +0200 )edit
If you could attach your test spreadsheet to this answer, that would be great, thanks.
James Haigh ( 2012-11-09 13:23:17 +0200 )edit
<crt j=""> sorry but this is rubbish. I don't know what happened, it should be <ctrl j> ( 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
ROSt52 ( 2012-11-09 15:42:46 +0200 )edit
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.
ROSt52 ( 2012-11-09 15:43:21 +0200 )edit

Number format code that multiplies by powers of 1000

Comments

2 Answers

Comments

Resources

Donate

Question tools

Stats

Related questions