Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I know that LibreOffice allows you to define custom number formats. I have a spreadsheet that displays some smallish percentages (all are less than 1%). I would like to use permille instead.

Percentages less than one percent cause confusion with people looking at the spreadsheet as percentages are often interchanged with decimals 0.34 -> 34%. I've often seen spreadsheets where .34% was written and people have assumed this meant 34%, also I've seen cases where the author meant 34% but the value in the cell was .0034

I looked at the Format code for percent to see if there was any special trick I could use, since for a percentage the decimal point is moved 0.5 -> 50%, which is different from changing the thousand separator or the number of decimal places.

The "Format code" for percent looks like this: 0.00%

It looks like the % symbol has a special interpretation and I can't just tell the format to multiply by 1000 and append the permille symbol as a work-around I am using

=ROUND({originalCell}*1000,2)&"‰"

but this changes the value and makes the cell mixed number/text. Is there any way I can create a custom format for this?

share|improve this question

I don't know how to do this with just the number format, but combining your workaround with the number format should solve the problem.

Adjust your workaround like so, leaving off the permille symbol:

=ROUND({originalCell}*1000,2)

Then use the permille symbol in the number format using quotes, like below.

0.00"‰"

That should keep the cell contents in number format while appearing with the permille symbol.

share|improve this answer
    
That's a slight improvement, at least I can have just a number this way. The value will still be out by a factor of 1000. In my version of Libre-office you can define the format as 0.00‰ (without quotes). – Loopo Jun 20 at 8:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .