The Microsoft Excel program allows you to work quickly with the percents: find them, summarize them, add them to the number, calculate the % increase, the percentage of the number, the amount, etc. Such skills can be useful in a wide variety of spheres of life.
In everyday life, we are increasingly faced with the percentage: discounts, loans, deposits, etc. Therefore, it is important to be able to calculate them correctly. Let's get acquainted with the techniques that the built-in toolkit of the tabular processor offers.
How to calculate the percentage from the number in Excel
Before calculating the percentage from the amount, let's define the term "percent". It has a Latin origin and is literally translated as "out of a hundred." This is a certain part of the 100 parts of the whole.
The mathematical formula for calculating percentage is as follows: (part / whole) * 100.
To find a percent of a number, use this variant of the formula: (number * percent) / 100. Or move the comma in percentage by 2 characters to the left and perform only multiplication. For example, 10% of 100 is 0.1 * 100 = 10.
The kind of formula to use in Excel depends on the desired result.
Task number 1: Find how much is 20% of 400.
- We make the active cell in which we want to see the result.
- In the formula line or directly into the cell, enter = A1 * B1.
Since we immediately applied the percentage format, we did not have to use the mathematical expression in 2 actions.
How to assign a percentage format for a cell? Choose any convenient way for you:
- Enter at once a number with the sign "%" (the cell will automatically set the required format);
- Click on the cell with the right mouse button (CTRL+1), select «Format Cells» - «Percentage»;
- Select the cell and press the combination of hot keys CTRL + SHIFT + 5.
Without using the appropriate format, the usual formula is entered in the cell: = A1 / 100 * B1.
This option of finding a percentage of the number is also used by users.
Task number 2: Ordered 100 items. 20 were delivered. Find how many percents of the order is completed.
- Set the desired cell percentage format.
- Enter the formula: = B1 / A1. Press ENTER.
In this task, we dealt with one action again. Private did not have to be multiplied by 100, because the percentage format is assigned to the cell.
Entering % in a separate cell is not necessary. We can have a number in one cell and the formula for finding a percentage of the number (= A2 * 20%) in the second.
How to add the percentage to the number in Excel?
In mathematics, we first find the percentage of the number, and then perform the addition. Microsoft Excel does the same. We need to enter the formula correctly.
Task: Add 20% to the number 100.
- The values are entered in cells with the appropriate formats.
- We enter the formula: = A1 + A1 * B1.
To solve the same problem, another formula can be used: = A1 * (1 + B1).
The difference between the numbers in percentages in Excel
The user needs to find the difference between the numeric values as a percentage. For example, calculate how much the supplier's price has increased / decreased, the company's profit, the cost of utilities, etc.
That is, there is a numerical value, which over time, due to circumstances, has changed. To find the difference in percent, you need to use the formula: (new price old price) / old price * 100%.
Task: Find the difference in percentage between the "old" and "new" prices of the supplier.
Let's make the third column «Dynamics in %». Assign a appropriate format for cells.
- Put the cursor in the first cell of the column, enter the formula: = (B2-A2) / B2.
- Press Enter. And stretch the formula down.
The difference in percentage is positive and negative. The establishment of appropriate format made it possible to simplify the original calculation formula.
The percentage difference between the two numbers in the default cell format («General») is calculated by the following formula: = (B1-A1) / (B1 / 100).
How to multiply on percents in Excel
Task: 10 kg of salt water contains 15% salt. How many kilograms of salt are in the water?
The solution is reduced to one action: 10 * 15% = 10 * (15/100) = 1.5 (kg).
How to solve this problem in Excel:
- Enter the number 10 in cell B2.
- Place the cursor in cell C2 and enter the formula: = B2 * 15%.
We did not have to convert percentages into numbers, because Excel perfectly recognizes the sign "%".
If the numeric values in one column and the percent in the other, then in the formula it is enough to make references to the cells. For example, = B9 * A9.
Calculation of the percentage of the credit in Excel
Objective: It was taken a credit for 200 000$ for a year. The interest rate is 19%. We will repay for the entire term equal payments. Question: what is the monthly payment amount under the given crediting conditions?
Important conditions for choosing a function: the consistency of the interest rate and the amounts of monthly payments. A suitable option is «=PMT()». It is located in the section «FORMULAS»-«Financial»-«PMT».
- Rate - the interest rate on the loan, divided by the number of interest accrual periods (19% / 12, or B2 / 12).
- Nper - the number of loan repayment periods (12).
- Pv - loan amount (200 000$, or B1).
- The arguments fields «Fv» and «Type» will be ignored.
The result is with a "-" sign, because the borrower will give the money back.