# Number format code that multiplies by powers of 1000 [closed]

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:

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-10-26 22:19:47.606072

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?

( 2012-10-30 02:14:20 +0200 )edit

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

( 2012-10-30 19:03:04 +0200 )edit

@James: 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.

( 2012-10-31 02:15:50 +0200 )edit

@James: 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

( 2012-10-31 02:27:49 +0200 )edit

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

( 2012-11-06 13:04:42 +0200 )edit

@James: 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"?

( 2012-11-06 15:46:31 +0200 )edit

@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.

( 2012-11-06 15:50:06 +0200 )edit

@ROSt52: 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'.

( 2012-11-08 13:40:41 +0200 )edit

@ROSt52: 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.

( 2012-11-08 13:52:11 +0200 )edit

@ROSt52: 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.

( 2012-11-08 14:06:57 +0200 )edit

Sort by » oldest newest most voted

Hi @James Haigh,

Did you ever find a solution to your formatting question?

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

Thanks!

more

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.

more

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.

( 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.

( 2012-11-09 13:18:51 +0200 )edit

( 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

( 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.

( 2012-11-09 15:43:21 +0200 )edit