How to Calculate Percentage Using Excel Formulas?
The percentage is calculated as the proportion per hundred. In other words, the numerator is divided by the denominator and the solution is multiplied by 100. The percentage convention in Excel is = Numerator/Denominator ( used without multiplication by 100 ). To convert the output to a share, either bid “ Ctrl+Shift+ % ” or click “ % ” on the Home pill ’ s “ number ” group .
Let us consider a dim-witted exemplar .
On a 15-day vacation, Mr. A spent 10 days in his hometown and 5 days in the USA. What percentage of days did he spend in the USA ?
The calculations are given as follows :
- Percentage formula=Portion days/Total days*100
- Percentage of days spent in the USA=5/15*100=33.33%
- Percentage of days spent in hometown=10/15*100=66.66%
hence, Mr. A spent approximately 33 % of his vacation period in the USA .
You can download this Formula for Percentage Excel Template here – Formula for Percentage Excel Template Let us go through the comply examples to understand the concept of share in Excel .
The following table shows the subject-wise marks of 6 students in a school. Since the results pertain to the annual examinations, the utmost marks of every subject are 100 .
We have to calculate the percentage of marks for all students .
The steps to calculate percentages in Excel are listed as follows :
- Calculate the total marks obtained by the students. For this, the marks of every subject are added.
- Drag the formula of cell H2 to get the total marks obtained by all students. The output is shown in the following image.
- Divide the total marks by 600. The values of the “total marks” column become the numerator. Since the number of subjects is 6, the maximum marks are 600 (100*6=600). This becomes the denominator.
- Apply the following formula.
Percentage=Marks scored/Total marks*100.
For percentage values ( shown in the future image ), change the cell format. blue-ribbon column I and press “ Ctrl+Shift+ %. ” alternatively, choose “ % ” in the “ number ” group of the Home yellow journalism.
The following table consists of students who undertake internship in an arrangement. Their tax is to sell products based on pre-defined targets .
We want to determine the efficiency horizontal surface ( percentage ) of every student .
The steps to calculate the efficiency charge are listed as follows :
Step 1: Apply the convention “ sales/target. ”
Step 2: Format column D to obtain the efficiency levels in share. For the last two students ( Gulp and Ganapathi ), the convention returns “ # DIV/0 ! ” error Excel Error Errors in excel are common and much occur at times of applying formulas. The list of nine most common excel errors are – # DIV/0, # N/A, # NAME ?, # NULL !, # NUM !, # REF !, # VALUE !, # # # # #, round Reference. read more .
Note: If the numerator is zero, the division of the numerator by the denominator returns “ # DIV/0 ! # DIV/0 ! # DIV/0 ! is the division error in Excel which occurs every time a act is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell. read more ” error .
Step 3: To eliminate the erroneousness, tweak the existing convention as “ =IFERROR ( C2/B2,0 ). ” This implies that the IFERROR IFERROR The IFERROR function in Excel checks a formula ( or a cell ) for errors and returns a specified value in rate of the error. read more displays zero if the division of C2 by B2 returns an error .
In early words, the “ # DIV/0 ! ” error is replaced by nothing with the serve of the modified rule.
Read more: How to Tie a Tie: 7 Knots for All Occasions
Note: The IFERROR function IFERROR Function The IFERROR affair in Excel is used to determine what to do when an error occurs before performing any affair. read more helps get rid of share errors .
Step 4: The output using the IFERROR serve is shown in the follow picture. The efficiency levels of students Gulp and Ganapathi are zero. This is because of the blank ( zero ) in column B and/or column C .
For 2018, an constitution ’ s sales for the months January and February are given in the come table .
We want to calculate the growth or the refuse ( share ) in monthly sales .
The steps to calculate the increase ( growth ) or decrease ( descent ) share are listed as follows :
Step 1: Apply the rule “ = ( C2-B2 ) /B2. ”
The sales in February ( 57500 ) are more than the sales in January ( 53250 ). The difference ( increase ) between the two sales is divided by the sales of the initial calendar month ( January ) .
Step 2: Format column D to obtain the emergence share .
Hence, the February sales have increased by 7.98 % over January sales .
Note: While calculating the growth or decline percentage, if the numerator or the denominator is less than zero, the output is a negative value .
Frequently Asked Questions
1. What is the Excel formula for percentage? The basic percentage convention is “ ( part/total ) *100 ”. This recipe is used in Excel without the latter part ( *100 ). This is because when the share format is selected, the resulting number is mechanically changed to percentage.
In summation, the decimal points are removed and the output is shown as a round off share. For exemplify, 26/45 is 0.5777. By applying the share format, this decimal fraction number becomes 58 %.
To convert the output to percentage, either press “ Ctrl+Shift+ % ” or select “ % ” in the “ numeral ” group of the Home yellow journalism. 2. What is the Excel formula for calculating the percentage change? The convention for calculating the percentage change is stated as follows :
“ ( New value-Initial rate ) /Initial value ”
This formula helps calculate the percentage increase or decrease between two values. A plus share implies an increase, while a minus percentage shows a decrease.
Note: To get the final answer ( percentage change ), the percentage format is applied to the output of the formula. 3. What is the Excel formula for calculating the percentage of the total? The recipe for calculating the percentage of the sum is “ ( part/total ). ” For case, column A lists the monthly expenses from cell A2 to cell A11. The cell A2 contains $ 2500 as the rent paid. The cellular telephone A12 contains the total expenses $ 98700.
The formula “ =A2/ $ A12 ” returns 3 % after applying the percentage format. This implies that the rent is 3 % of the total paid expenses.
Since A2 should change on dragging the formula to the remaining cells, it is entered as a relative reference. On the early hand, the total should remain fixed for all cells, so A12 is entered as an absolute reference.
- The percentage is the proportion per hundred.
- To calculate the percentage, the numerator is divided by the denominator and the result is multiplied by 100.
- For obtaining percentages, the output column is formatted by pressing “Ctrl+Shift+%” or selecting “%” in the Home tab.
- The division of the numerator by the denominator returns “#DIV/0!” error if the former is zero.
- The percentage errors can be removed with the help of the IFERROR function.
- The increase or decrease percentage is calculated by dividing the difference between two numbers with the initial number.
This has been a guide to formula for share in Excel. here we discuss the calculation of Excel percentages using the IFERROR recipe along with Excel examples and downloadable Excel templates. You may besides look at these useful functions in Excel –
Read more: The Best Classic Chili – The Wholesome Dish