Description: Numbers can be formatted with % (percent), but it is not possible to format numbers with ‰ (per mille). 0.001 = 0.1% = 1‰ Alternative spellings: per mil, per mill, permil, permill, or permille Steps to Reproduce: 1. Open Calc 2. Enter 1‰ 3. Open Format > Cells... Dialog Actual Results: 1‰ is recognized as text and not treated as number. Expected Results: 1‰ is a formattable number similar to %. Calculation with ‰ is possible similar to %. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.0.0.0.alpha0+ Build ID: c5a909501939726ff0ce018129f535fd03092ee2 CPU threads: 4; OS: Linux 4.12; UI render: default; VCL: x11; Locale: de-CH (en_US.UTF-8); UI-Language: en-US Calc: threaded
I think you can get it with the format: 0,00."‰" this is with a language with comma as decimal separator. https://help.libreoffice.org/7.0/en-US/text/scalc/guide/format_value_userdef.html?DbPAR=CALC#bm_id3143268
Thanks for the input. I've tested it. The example in the help with million (0.0,, "Million") works, however '0,00."‰"' does not work. For million the thousands delimiters (,) is used which acts as division by 1000 for display. However for ‰, there seems to be no sign, which acts as multiplication by 1000 for display. I've tested it in English (USA) locale.
[Automated Action] NeedInfo-To-Unconfirmed
Another similar use case is cents. E.g. the cell is in €/km, but is should be shown cents/km. Example: Cell: 0.07 €/km Display: 7 cents/km Display = 100 * cell value
I plan to introduce this notation in 5 steps 1. accept format with permil sign 2. accept permil sign when editing cell 3. save/open this new format 4. create new default format with permil 5. add unit test for this format permil and permyriad (‱) can be simultaneous treated. However specific format, such as "cent/km", is not in the scope of the resolution I propose, because spreadsheet treat only number and is totally ignorant of dimensions of quantities (exception for time).
Does Excel support those? If not, such format code can't be exported/roundtripped in .xls/.xlsx and I'd advise against implementing it. Furthermore, ODF would need an extension for office:value-type="permille" (calcext:value-type="permille") and a new <number:permille-style> (<loext:permille-style>) element. Same for office:value-type="permyriad" (calcext:value-type="permyriad") and <number:permyriad-style> (<loext:permyriad-style>) but I highly doubt the usefulness of that. Also, ‰ ‱ are easily visually confusable. Is that really worth the hassle?
(In reply to Eike Rathke from comment #6) > Does Excel support those? If not, such format code can't be > exported/roundtripped in .xls/.xlsx and I'd advise against implementing it. > > Furthermore, ODF would need an extension for office:value-type="permille" > (calcext:value-type="permille") and a new <number:permille-style> > (<loext:permille-style>) element. > > Same for office:value-type="permyriad" (calcext:value-type="permyriad") and > <number:permyriad-style> (<loext:permyriad-style>) but I highly doubt the > usefulness of that. Also, ‰ ‱ are easily visually confusable. > > Is that really worth the hassle? As far as I know, Excel does not support permille notation. From my point of view, there no need to create an extension of ODF. I propose to treat permille as percent. Only the different sign is needed in text string which is exported by ODF. See https://gerrit.libreoffice.org/c/core/+/95169 Nothing to change to export. Previous version of LibO will display it as a number (with correct value) with an extra string. I added permyriad because it had almost no cost, as it is treated like permille. But no idea of its usefulness ;-)
(In reply to Eike Rathke from comment #6) > Does Excel support those? If not, such format code can't be > exported/roundtripped in .xls/.xlsx and I'd advise against implementing it. I'm going to explore problems around Excel export.
(In reply to Laurent BP from comment #8) > (In reply to Eike Rathke from comment #6) > > Does Excel support those? If not, such format code can't be > > exported/roundtripped in .xls/.xlsx and I'd advise against implementing it. > I'm going to explore problems around Excel export. In https://gerrit.libreoffice.org/c/core/+/95186 I propose to export permille and permyriad format as percent for Excel and for strict ODF. Export of permille and permyriad is limited to ODF extended.
Nice to see this! Would be cool if `=5+5‰` would also work like `=5+5%` in cell calculations. :)
Dear Laurent BP, This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW. Please assign it back to yourself if you're still working on this.