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:
http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/
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?
@ROSt52: For example, I want to format
0.00001234567
as12.346μ
.@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.
@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
@ROSt52: Did you find anything? I don't have the proprietary alternative to actually try the test you suggest.
@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"?
@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.
@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 with0.###"text"
; you should get something like '1.text' rather than '1text'.@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.@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.