How to calculate average percentage in excel. All ways to calculate interest in Microsoft Excel - formulas and examples of use. Calculation of value and total amount

    Program Microsoft Excel makes our life easier. It is very easy to do any operation. To do this, just write the formula on the command line.

    To deduct interest, you need to perform the following steps. In the column where the result should be, put the cursor. Then comes the formula - we write down the number from which we need to subtract the percentages, or simply point to the cell with the percentages, if we have them. We put a minus sign and open the bracket. In brackets, you need to write the following actions - from the required number we subtract the percentage that needs to be subtracted. It can be written by hand or also through the cell.

    As a result, we should get, for example, such a formula (number -(number * percentage to subtract). We press Enter.

    1. So first you need to select the cell where the percentage will be calculated and the result will be displayed. In this cell we put the sign = (equals)…
    2. Then we find that cell with the number from which we need to calculate the percentage and enter the e address in the selected cell ...
    3. Behind the cell address we put the sign * (multiplication)…
    4. Behind the multiplication sign, we write the percentage that we need to calculate, or select the cell in which this percentage is already registered and enter the address ...
    5. Following the number or address line, put the sign % (percentage)…
    6. Then you just need to press Enter (enter)…

    You will have a figure, which will be a given percentage number ...

    It looks something like this:

    =G9*H15%

    If in the Excel program it is necessary to subtract a certain number of percentages from a number, then this action is written in the form of a formula for the following steps

    • to begin with, we stand on an empty cell where we would like to see the final result;
    • we start the formula with the sign = ;
    • specifies the number from which to subtract the percentage. If this number is already written in the file, then click on the cell where it appears with the mouse;
    • put the subtraction sign -;
    • open bracket (;
    • again click on the number from which we subtract the percentage;
    • put the multiplication sign *;
    • write the number of percent, after which you must specify the symbol %;
    • close the parenthesis;
    • press Enter.

  • I will try to summarize your question. The algorithm of actions is as follows:

    • in an empty cell we put the sign =.
    • write the address of the cell from which we subtract the percentage.
    • we write the sign *.
    • we write the number of percent and the sign %.

  • The subtraction equation is: amount/total=percentage(decimal percentage format)

    Suppose you write down the formula =A2 / C2 in cell D2 and copy it down as many lines as you need, for this you can use the autofill marker. Then press the Percent Style command on the Home tab in the Number command group. signs to the right of the comma.

    In this program, you can find out the percentage of the total in two ways;

    The first function is fixed in the control panel itself, where at the top, approximately in the middle, is a mat. panel shows the % icon.

    The second option is to write it in a line and subsequently perform a calculation.

    If you often have to deal with percentages, I can suggest you automate this process. To do this, you will need to create a small template for yourself. Let me explain:

    In cell G6, the formula =E6-(E6*F6) is entered. Cell F6 is formatted as a percentage. If you, for example (as in the example), need to subtract 10% from 100, then you enter the number 100 in cell E6, the desired percentage in cell F6 and in the final cell G6 you get the result.

    Choose functions, then mathematical, and there%. Although it is more convenient for me to write the formula myself. % is a part of 100. Therefore, if you need to subtract, for example, 27%, then I will write it as a formula: (column, where the desired number) * 0.73.

    For example, subtract 10% from 100. We enter the formula = 100- (100 * 0.1) It turns out 90. Well, then it depends on your imagination, you can make this process much easier (if you often need to add and subtract percentages) by breaking these actions into cells and percentages.

    Subtracting percentages in Excel will be quite simple. To do this, select an empty cell and put an equal sign there. Then we look at the numerical address of the cell from which the percentage must be subtracted. We insert this address after and put the sign multiplyquot ;. Then we insert or prescribe manually the percentage that we subtract. Then put the percent sign and press enter.

    To subtract a percentage from a number in this program, you need to do the following:

    • put the cursor in an empty cell in which we will have the result,
    • we begin to draw up a formula, first we write the sign = ;
    • then we write the number from which we subtract percentages, you can also click on it if the number is already in the cell,
    • then we write the sign -;
    • then put the sign (;
    • click on the number from which you want to subtract percentages,
    • then we write the multiplication sign * and indicate the percentage
    • then we write the sign %, and then );

    now it remains to press Enter.

Microsoft Excel allows you to quickly work with percentages: find them, sum them up, add them to a number, calculate percentage growth, percentage of a number, of a sum, etc. Such skills can be useful in a wide variety of areas of life.

AT Everyday life we are increasingly confronted with interest: discounts, loans, deposits, etc. Therefore, it is important to be able to calculate them correctly. Let's take a closer look at the techniques offered by the built-in spreadsheet toolkit.

How to calculate percentage of a number in Excel

The mathematical formula for calculating interest is as follows: (search part / integer) * 100.

To find the percentage of a number, the following version of the formula is used: (number * percentage) / 100. Or move the comma as a percentage by 2 digits to the left and perform only multiplication. For example, 10% of 100 is 0.1 * 100 = 10.

Which formula to apply in Excel depends on the desired result.

Task #1: Find how much is 20% of 400.

  1. We make the cell in which we want to see the result active.
  2. In the formula bar or directly into the cell, enter =A2*B2.

Since we immediately applied the percentage format, we did not have to use a mathematical expression in 2 steps.

How to assign a percentage format to a cell? Choose any method convenient for you:

  • immediately enter a number with the sign "%" (the cell will automatically set the desired format);
  • right-click on the cell, select "Format Cells" - "Percentage";
  • select a cell and press the hot key combination CTRL+SHIFT+5.

Without using the percentage format, the usual formula is entered into the cell: \u003d A2 / 100 * B2.

This option for finding a percentage of a number is also used by users.

Task #2: 100 items ordered. Delivered - 20. Find how many percent of the order is completed.

  1. Set the required cell format to percentage.
  2. Enter the formula: =B2/A2. Press ENTER.

In this problem, we again managed with one action. The quotient did not have to be multiplied by 100, because the cell is formatted as a percentage.

It is not necessary to enter percentages in a separate cell. We can have a number in one cell. And in the second - the formula for finding the percentage of the number (= A2 * 20%).



How to add percentage to a number in Excel?

In mathematics, we first find percentages of a number, and then we perform addition. Microsoft Excel does the same. We need to enter the formula correctly.

Task: Add 20 percent to the number 100.

  1. We enter the values ​​in cells with the appropriate formats: number - with a numeric (or general), percentage - with a percentage.
  2. Enter the formula: =A2+A2*B2.

Another formula can be used to solve the same problem: =A2*(1+B2).

Difference between numbers as a percentage in Excel

The user needs to find the difference between the numerical values ​​as a percentage. For example, calculate how much the price of the supplier has increased / decreased, the profit of the enterprise, the cost of utilities, etc.

That is, there is a numerical value that has changed over time, due to circumstances. To find the percentage difference, you must use the formula:

("new" number - "old" number) / "old" number * 100%.

Task: Find the percentage difference between the "old" and "new" prices of the supplier.

  1. Let's make the third column "Dynamics in percent". Let's assign a percentage format to the cells.
  2. Put the cursor in the first cell of the column, enter the formula: = (B2-A2) / B2.
  3. Let's press Enter. And drag the formula down.

The percentage difference has a positive and a negative value. The establishment of the percentage format made it possible to simplify the original calculation formula.

The percentage difference between two numbers in the default cell format ("General") is calculated using the following formula: =(B1-A1)/(B1/100).

How to multiply by percentage in Excel

Task: 10 kg of salt water contains 15% salt. How many kilograms of salt are in the water?

The solution comes down to one action: 10 * 15% = 10 * (15/100) = 1.5 (kg).

How to solve this problem in Excel:

  1. Enter the number 10 in cell B2.
  2. Place the cursor in cell C2 and enter the formula: \u003d B2 * 15%.
  3. Press Enter.

We didn't have to convert the percentage to a number because Excel recognizes the "%" sign perfectly.

If a numerical values in one column, and percentages in another, then it is enough to make cell references in the formula. For example, =B9*A9.

Calculation of interest on a loan in Excel

Task: They took 200,000 rubles on credit for a year. Interest rate - 19%. We will repay over the entire term in equal installments. Question: what is the amount of the monthly payment under these credit conditions?

Important conditions for choosing a function: the constancy of the interest rate and the amounts of monthly payments. A suitable variant of the function is "PLT ()". It is located in the section "Formulas" - "Financial" - "PLT"


  1. Rate - the interest rate on the loan divided by the number of interest periods (19%/12, or B2/12).
  2. Nper is the number of loan payment periods (12).
  3. PS - loan amount (200,000 rubles, or B1).
  4. The fields of the arguments "BS" and "Type" will be ignored.

The result with the "-" sign, because the borrower will repay the money.

If you are reading this material, then you probably already have some idea about how Excel works. Accordingly, you know that this program is the best suited for various kinds of calculations. Thus, everything that you previously calculated in your mind or on a calculator can be easily calculated in Excel, which, of course, significantly saves time for people in the relevant professions.

In order not to beat around the bush for a long time, I will tell you about how to subtract interest in excel. Again, you are probably used to doing this in your head, however, as well as elementary, but why bother if you have an application in front of you that can do all this by itself? Let's take a closer look.

What is interest?

If you don't mind, I'll make a short excursion to school course mathematics, when you went through this topic and, perhaps, forgot a little. So, a percentage is actually some part of a whole number. To calculate the percentage, you need to divide the desired figure by an integer value, and then multiply the result by 100.

Simple, right? Performing this operation in Excel is even easier, and now you will see for yourself.

How to subtract a percentage

Let's imagine that any percentage is some kind of unity. Well, for example, 15% can be changed as 0.15, 138% - as 1.38, etc., respectively, say, the formula "=200+15%" is no different from the formula "=200+0.15". This was a small introduction, now I propose to move on to the practical part, written in the form of examples based on the above 200 and 15%.

Each of these methods is good in its own way, and each will find its user. Choose the most convenient and understandable for you!

In almost every field of activity, we are faced with the need to be able to calculate percentages. Most often, we need interest calculation skills when calculating VAT, margins, markups, interest on loans, returns on deposits and securities, and discounts. All these calculations are made as a calculation of a percentage of the amount.

Click on the button below to download an Excel file with examples of interest calculations in Excel:

What is a percentage?

Percent (Latin per cent - per hundred) - one hundredth part. Marked with a "%" sign. Denotes the proportion of something in relation to the whole. For example, 25% of 500 rubles. means 25 pieces of 5 rubles. each, that is, 125 rubles.

The basic formula for calculating the percentage of the amount looks like this:

There is no one size fits all formula for every interest calculation scenario. Below you will learn about the most popular payment methods.

How to calculate percentage of an amount in Excel

Video lesson:

(Part/Whole) * 100 = Percentage (%)

But if you use the “Percentage” cell format, then to calculate the percentage of a number, it is enough to divide one number by another. For example, we have apples that we bought for 100 rubles. in a wholesale warehouse, and we will set the retail price of 150 rubles. In order to calculate the percentage of how much the purchase price is from the retail price, we need:

  • Create a table with prices and add a column to calculate the percentage:
  • In cell D2, enter a formula that calculates the percentage of the purchase price from the sale price:
  • Apply cell format D2 “Percentage”:

How to calculate the percentage of the sum of the values ​​​​of an Excel table

Video tutorial:

Let's imagine that we have a list of sellers with the sales volume for each and the total amount of all sales for all sellers. Our task is to determine what percentage is the contribution of each seller to the final turnover:

For this we need:

  • Add a column to the table with percentage calculation;
  • In cell C2, insert the formula:

The $ signs fix column “B” and cell “9” so that when dragging the formula to all rows of the table, Excel automatically substitutes the sales volume of each seller and calculates the % of total sales. If you do not put the “$” signs, then when dragging the formula, the system will shift cell B9 as many cells down as you drag the formula.

  • Extend the formula to all table cells corresponding to rows with sellers' last names:
  • In the example above, we stretched the formula and got the values ​​​​in the form fractional numbers. In order to convert the received data into percentages, select the data with the left mouse button and change the cell format to “Percentage”:


How to calculate the percentage of multiple values ​​from the sum of a table

Video tutorial:

In the example above, we had a list of sellers and their sales volume. We calculated what contribution each of the employees made to the final sales volume. But what if we have a list of recurring products with sales data and we want to calculate how much of a particular product is a percentage of all sales?

From the list of products above, we want to calculate how much sales are tomatoes (they are written in several rows of the table). For this:

  • To the right of the table, we indicate the product (Tomatoes), for which we want to calculate the share in sales:
  • Change the format of cell E2 to “Percentage”;
  • In cell E2, insert a formula with a function that will help you calculate Tomatoes from the list of products and sum up their sales, and then divide it by the total sales of products:


How does this formula work?

For calculations, we use the formula. This function returns the sum of the numbers given as arguments that meet the criteria specified in the formula.

The syntax for the SUMIF function is:

=SUMIF(range, condition, [sum_range])

  • range– the range of cells by which the criteria are evaluated. The argument can be numbers, text, arrays, or references containing numbers;
  • condition– criteria that are checked against the specified range of cells and determine which cells to summarize;
  • sum_range are summed cells. If this argument is not specified, then the function uses the argument range as sum_range .

Thus, in the formula =SUMIF($A$2:$A$8,$E$1,$B$2:$B$8)/B9 we indicated " $A$2:$A$8” as a range of goods, among which the function will search for the criterion we need (Tomatoes). Cell " $E$1” is specified as a criterion and indicates that we are looking for “Tomatoes”. Cell range “ $B$2:$B$8” denotes which cells need to be summarized if the desired criterion was found.

How to calculate percentage difference in Excel

For example, to calculate the percentage difference between two numbers A and B, use the formula:

(B-A)/A = Percent difference between two numbers

In practice, when using this formula, it is important to determine which of the numbers you need is “A” and which is “B”. For example, let's say that yesterday you had 8 apples in stock, and today there are 10 apples. Thus, the number of apples relative to yesterday has changed by 25% upwards. In the event that you had 10 apples yesterday, and today there are 8 apples, then the number of apples decreased by 20% compared to yesterday.

Thus, the formula that allows you to correctly calculate the percentage change between two numbers looks like this:

(New value - Old value) / Old value = Percentage difference between two numbers

Below, using examples, we will analyze how to use this formula for calculations.

Sometimes calculating percentages can be difficult, because it is not always easy to remember what we were taught in school. Let Excel do the work for you - simple formulas can help you find, for example, the percentage of the final value or the difference between two numbers as a percentage.

Important: The calculated results of formulas and some Excel worksheet functions may be slightly different between Windows x86 or x86-64 computers and Windows RT ARM computers.

Let's say your company sold $125,000 worth of goods this quarter and you need to calculate what percentage of $20,000 is of the total.

In 2011, the company sold goods in the amount of 485,000 rubles, and in 2012 - in the amount of 598,634 rubles. What is the difference between these percentages?

additional information

Calculating the Percentage of the Total Value

Let's say that you answered 42 questions out of 50 correctly on the test. What is the percentage of correct answers?

Calculating the difference between two numbers as a percentage

Suppose your salary was 23,420 rubles in November and 25,000 rubles in December. By what percent did your salary change in December compared to November? Then, if in January you earned 24,250 rubles, then by how many percent does this differ from December? You can calculate the difference by subtracting the new salary from the previous salary and then dividing the result by the sum of the previous salary.

Magnification Percentage Calculation

Reduction Percentage Calculation

Finding the total when you know the amount and percentage

Let's say the sale price of a shirt is $15 and the discount is 25% off the original price. What are the starting prices? In this example, you need to find 75% of the number equal to 15.

Finding the sum when you know the total and the percentage

Let's say you want to purchase a computer for $800 and pay an additional 8.9% in sales tax. How much do you have to pay for sales tax? In this example, you need to find 8.9% of 800.

Increase or decrease a number by a given percentage

Let's say you spend an average of $113 for food per meal each week and you want to be able to raise your food costs by 25%. How much can you spend on this? Or, if you want to reduce your $113 groceries off by 25%, what's your new weekly discount?

Increasing a number by a given percentage

Decrease a number by a given percentage

mob_info