How to handle parts per million, basis points and per mille in Excel

Excel has built-in functionality to handle percent (%), but there is no automatic way to calculate parts per million, basis points, permyriad or per mille in Excel. It is easy to calculate though, but let’s start with the definitions:

And here’s how to apply it:

You can use the Unicode numbers from the table above with the function UNICHAR to get the symbols in Excel:

Percent

Excel has made it easy to calculate percent. In this example, you simply divide the discount by the price and hit the Percent Style button, and the result of the formula (0.15) is shown as 15%.

Per mille

Per mille is not very commonly used in the English-speaking world, but it’s more common in Europe. Some examples of use include: Legal limits of blood-alcohol content when driving, Seawater salinity, Tunnel and railway gradients, Birth and death rates, and Property taxation rates (millage rate or mill rate)

Permyriad (basis points)

The word myriad comes from Ancient Greek and means ten thousand. In modern Greek, the word for a million is εκατομμύριο (ekatommýrio), which means “hundred myriad” or 100 x 10,000. Permyriad, which means parts per ten thousand is very rarely used, but the related basis point is commonly used in finance, meaning 0.01 percentage points. Basis points are very easy to calculate, as they behave the same way as percentage points, i.e. the difference between 3.00% and 3.10% is ten basis points (or 0.10 percentage points), and not 3.33% (or 333 permyriad!).

Parts per million

Parts per million is mostly used in science and engineering. In Excel, you can use the same calculation as in “per mille” above – just add three more zeroes.

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!

How to handle parts per million, basis points and per mille in Excel

More Excel tutorials:

Leave a Reply Cancel reply