How to Calculate Percentage as a Proportion in Excel
Related Page:
Percentage of a Number
If you want to calculate percentage as a proportion (i.e. calculate the size of a sample, as a percentage of a full set), this is done by dividing the sample size by the size of the full set.
For example, if you take an exam and answer 34 questions correctly, out of a total of 40 questions, you can calculate the percentage of correct answers by typing the following formula into any Excel cell:
=34/40
- which gives the result 0.85, or 85%.
As with any Excel formula, the above percentage calculation can use values stored in your spreadspeed, instead of actual numbers. This is shown in the Excel spreadsheet below.
|
Formula:
|
Result:
|
Note that, by default, the result in cell B3 of the above spreadsheet will usually be displayed as the decimal 0.85. Therefore, if you want to display this result as the percentage 85%, you may need to apply percentage formatting to cell B3.
How to Format Cells as Percentages
The easiest way to format cells as percentages is to select the cell(s) to be formatted and then select the percentage button from the 'Number' group on the Home tab of the Excel ribbon (see below):
This uses the default percentage formatting style for your computer system which might be exactly what you require.
However, if you want to change the number of decimal places that are displayed, this can be done within the 'Format Cells' Dialog Box, which is brought up by clicking on the dialog box launcher (shown in the above image).
Within 'Format Cells' dialog box:
- From within the 'Category' list on the left side of the dialog box, select the Percentage type.
- The Decimal places: option will appear in the dialog box. Use this to specify the number of decimal places that you want to display.
- Click OK.
For further examples of Calculating Percentage as a Proportion in Excel, see the Microsoft Office website.
Or, for an overview of different types of percentage calculation, see the Percentages In Excel page.
#DIV/0! Formula Error
When using the above percentage formula, you may occasionally get the #DIV/0! error. This occurs if the denominator (the number that you are dividing by) is zero.