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:
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 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.
More Excel tutorials:
- How to Calculate Grades in Excel
- How to Find Duplicates and Triplicates in Excel
- Create a report in Excel in 5 minutes – Beginner’s tutorial
Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages: