How to enter interest in Excel. Basic concepts of interest. Determining the percentage of numbers

Today, many people every day have to solve small arithmetic problems: calculating income and expenses, interest rates on loans and deposits. Very often we are faced with a problem - how to calculate the percentage in Excel or using a calculator? Many prefer the latter, fearing an incomprehensible foreign name. In fact, with minimal computer skills, you can make your computing life much easier. Let's solve these problems with the calculation of percentages in Excel using examples.

What percentage is a number from another number

The monthly electricity limit is 200 kW. During the first week, 50 kW were consumed. How many percent of the monthly limit have been used?

Calculating with a calculator: divide 50 by 200, multiply by 100 - we get 25%.

Let's do the same on the Exel page. Click on an empty cell (in our case, A1), enter 200, in the adjacent (B1) - 50. Click on the next cell - C1. A formula will be entered into it, let's do it in the simplest and most understandable way:

• enter the sign "=" (equal), click on B1 with a value of 50, enter the sign "/" (division), click on A1 with a value of 200,
• we enter the sign "*" (multiplication), enter 100 (in cell C1 we observe the combination = B1 / A1 * 100), press ENTER - we get the required number 25. Instead of the combination * 100, you can enter the "%" sign (= B1 / A1%)- the result will be the same;
• select the cell with the result C1, on the "Number" tab of the "Home" ribbon, change its format to "Percentage", and remove the "%" sign in the formula (= B1 / A1), we get 25.00%;
• changing numbers in cells. A1 and B1, in cell C1 you will always see the percentage of the number in B1 of the number contained in A1.

Prompt. You can enter cell numbers in the formula either by directly clicking on them, or manually.

The formula itself can be entered directly in the cell after double-clicking on it, as well as in the formula bar.

Calculations in Excel is convenient to carry out not only in the organization, but also to account for the family budget

What percentage is the sum of numbers from a number

Let's complicate the first example. Electricity consumption in the first week was 50 kW, in the second - 45 kW, in the third - 47 kW. The question is the same - how to calculate how much the monthly limit has been spent:

1. In the first cell A1 we enter the monthly limit - 200, in the next - the power consumption by weeks, respectively: B1 - 50, C1 - 45, D1 - 47.
2. Select the next cell E1, on the "Editing" tab of the "Home" ribbon, press the autosum icon, after which a blue frame appears.
3. Changing the size and position of the blue frame, we combine the cells, the values ​​of which need to be summed up - B1, C1, D1.
4. In the cell. E1 the formula = SUM (B1: D1) should appear, press enter.
5. Select cell F1, change its format to "Percentage", enter the formula into it: = E1 / A1, press Enter, we get the result - 71%.
6. If the subtotal is not needed, then in the formula of cell E1, the amount must be divided by the content of A1, adding the “/” sign and clicking on the cell. A1 - the formula will take the following form: = SUM (B1: D1) / A1.
7. We change the format of cell E1 to "Percentage", we get the same result - 71%.

Do not forget that any formula in Excel begins with the "=" sign, without which the program defines the input information as ordinary data, and not as a formula - an error message will appear.

To enter a formula in a specific cell, you first need to put the "=" sign

What is the percentage of the number

Very often the inverse problem arises - how to calculate the percentage of a number in Excel? Let's say you sell a product, say, irons, at a price of 1,500 rubles per item. VAT is 20%. How much will it be in rubles? We will not pick up a calculator, we will immediately turn to the Excel file:

• in the first cell A1 we will enter the price of our iron - 1500;
• in the second B1, having previously changed its format to "Percentage", enter the value of VAT - 20;
• into the cell with the result C1, enter the formula: = A1 * B1, press ENTER - we get the result: 300 rubles.

If the percentage is always constant, then you can shorten the formula and remove one cell with percentages. It will look like this:

1. In A1, as in the previous example, we enter the price of 1500.
2. In the second cell B1, enter the formula: = A1 * 20%, press ENTER, we get the result - 300 rubles.

If you need to make the same calculations for several products, then it is not at all necessary to enter formulas for each of them. For example, if several prices are entered in column A, VAT for A1 is calculated in cell B1, then in order to calculate it for A2 and other cells in column A, you need to click on B1 - a black frame will appear around it. Grabbing the lower right corner with the mouse (indicated by a small square, when you place the mouse on which a black cross appears), drag it down to the required cells - the formula will be copied into them.

Let's say you want to calculate the price of an item with VAT. This value can be calculated in the next cell C1. For this:

• make a double click on C1, on the "Editing" tab of the "Home" ribbon, press the sum icon;
• moving (squeezing, stretching) the blue frame that appeared after pressing the sum sign, select A1 and B1, in C1 should
• the formula = SUM (A1: B1) appears;
• press ENTER, we get the result - 1800 rubles.

Note, in this way, you can make a whole table with a list of all goods, where their cost and VAT are indicated.

Mathematical operations related to interest are relevant not only in the field of trade and economics. Such calculations are necessary to solve various problems in chemistry and physics, medicine, cooking, construction and many other industries.

Knowledge of calculations in Excel can simplify the accounting of an entrepreneur

What is the percentage of the sum of the numbers

• first, let's sum up all the values: select B11, press the sum icon;
• with a blue frame, we combine all the cells with the summed values ​​(we get the formula = SUM (B1: B10)), press enter;
• in B12, having previously changed its format to "Percentage", enter the value of VAT - 20;
• in the cell with the result B13, enter the formula: = B11 * B12, press ENTER - we get the desired result.

If we do not need intermediate results of the summation, and the value of VAT is constant, then the calculations can be reduced to one formula - the amount must be multiplied by 20%. In this case, the formula will look like: = SUM (B1: B10) * 20%.

If a mistake is made when entering a formula, an exclamation mark appears next to the cell. To edit a formula, you need to select the cell containing it, make the necessary changes in the formula bar. Use your mouse to select new cells or ranges.

In order to master the basics of working with Excel, you need very little time and knowledge - it's not much more difficult than online games or social networks. Adding, subtracting, calculating percentages, creating pivot tables, keeping summary records - Excel has a lot of features that can help us every day.

When working with numbers in Excel, it is often necessary to calculate the percentage of a number. For users with extensive experience in Excel, this is not a problem, but if you have started to study this program recently, then such a simple task can drive you into a dead end.

If you are faced with just such a problem, then this article should help you. Learn here how you can perform this operation in Excel 2003, 2007, 2010, 2013, or 2016.

If you need to calculate the percentage of a number in Excel, then the easiest way is to use the school curriculum, dividing the number by 100 and multiplying by the desired percentage. For example, in order to get 15 percent of the number from cell A2, you need to use the following formula: = A2 / 100 * 15... The screenshot below shows how this formula will look in Excel.

If necessary, the percentage can not be written directly into the formula, but taken from the table. In this case, instead of a specific percentage value in the formula, you need to specify the cell address with the percentage. As a result, the formula will look something like this: = A2 / 100 * B2

It should be noted that the formula with which you can calculate the percentage of the number can be written a little differently. You can not divide by 100, as in school, but immediately multiply by the desired percentage. In this case, the formula will look like this: = A2 * B2% where A2 is the original number and B2 is the percentage to be calculated. The main thing is not to forget to insert the percent sign at the end of the formula.

But, if the cell in which the percentage is indicated is not a numeric, but a percentage format, then in this case the percent sign at the end of the formula does not need to be indicated, and the formula looks like this: = A2 * B2 where A2 is the original number and B2 is the percentage to be calculated.

How to calculate the percentage of a number and immediately add it

Also, sometimes it becomes necessary to calculate the percentage of the number and immediately add it to this number. In this case, it is convenient to use the second way of writing the formula, without the school division by 100. If you write the percentage directly into the formula, it will look something like this: = A2 + A2 * 18%, where A2 is the original number, and 18 is the percentage to be counted and added.

In the process of solving various kinds of tasks, both educational and practical, users often turn to Excel.

Spreadsheets allow you to analyze data, create charts and graphs, and perform a variety of calculations. One of the most common operations is the calculation of percentages. The ability to competently make the necessary calculations is a useful skill that is successfully applied in almost all spheres of life. What techniques can you use to calculate percentages using Excel spreadsheets?

How to Calculate Percentage in Excel - Basic Calculation Formula

Before proceeding with the calculation of interest, it is necessary to define the terminology. The term "percentage" means the number of shares out of all 100 parts of a whole. The mathematical definition of percentage is a fraction, the numerator of which determines the required number of parts, and the denominator is the total. The result is multiplied by 100 (since the integer is 100%). Working with a spreadsheet, the formula for determining the percentage looks like this:

Part / whole = Percentage

The only difference from the usual in mathematics interpretation is the absence of further multiplication by 100. The properties of the table fields will help to obtain the required value format - just activate the Percentage format of the cell.

Example 1

Here is a row of data entered, for example, in column D (D2, D3, D4, D5,…). Calculate 5% of each value.

• Activate the cell adjacent to the first value (or any other) - the result of calculations will be located in it.
• In cell E2 write the expression "= D2 / 100 * 5" or "= D2 * 5%".
• Press Enter.
• Stretch cell E2 as many rows as needed. Thanks to the autocomplete marker using the above formula, the calculation will be made for the rest of the values.

Example 2

There are 2 columns of values ​​in front of you - for example, the realized cakes (D2, D3, D4, D5,…) and the total number of baked goods (E2, E3, E4, E5,…) of each type. It is necessary to determine what part of the product is sold.

• In the cell where the result will be calculated (for example, F), write the expression "= D2 / E2".
• Press Enter and "stretch" the cell for the required number of lines. Using the autocomplete marker will allow you to apply this formula to all subsequent cells and make correct calculations.
• To convert the result into percent format, select the required cells and use the Percent Style command. To activate the latter, you can right-click and select the item "Format cells" - "Percentage" in the list that appears. In doing so, you specify the desired number of decimal places. Or go to the "Home" - "Number" section and select the "Percentage" view.

How to calculate percentage in Excel - percentage of amount

To calculate the fraction of each part relative to the total, use the expression “= A2 / $ A $ 10”, where A2 is the value of interest and the total is in cell A10. What if the position you are interested in appears in the table several times? In this case, use the SUMIF function with parameters:

SUMIF (range, criteria, sum_range) / total

SUMIF (range, criterion, sum_range) / total

• Move to the cell where the result will be obtained.
• Write down the expression "= SUMIF (C2: C10; F1; D2: D10) / $ D $ 14" (or = SUMIF (C2: C10; F1; D2: D10) / $ D $ 14), where

C2: C10, D2: D10 - ranges of values ​​within which calculations are performed,

F1 - the cell in which the investigated characteristic is indicated,

D14 is the cell in which the amount is calculated.

How to Calculate Percentage in Excel - Percentage Change

The need for such calculations often arises in the course of assessing the gain or loss based on performance. So, the sales volumes by product category for 2015. entered in column D, similar data for 2016. - in column E. It is necessary to determine by what percentage the volume of sales has increased or decreased.

• In cell F2, enter the formula "= (E2-D2) / D2".
• Convert cell data to Percentage format.
• To calculate the increase or decrease for the remaining categories (cells), stretch F2 for the required number of lines.
• Evaluate the result. If the value is positive, you have an increase; if it is negative, you have a decrease.

The Excel spreadsheet editor is rightfully considered the best. In its functionality there are many tools that are designed to facilitate the user's work. No article will be enough to list them all, but talking about some of the features is another matter.

This article will discuss how to calculate percentages in Excel. Several ways will be demonstrated by which you can easily make the necessary calculations.

Formula

Now let's look at its use. As an example, we will use the numbers 9 and 17, where 9 is a number and 17 is the total.

Presets

However, you should not immediately enter the formula, initially, before calculating percentages in Excel, you need to change the value to "Percentage" in the "Number" group on the toolbar, you can see a more precise location of this option further on in the photo.

Example

Now let's go directly to the example. In order to find out the percentage of the number 9 of 17, you must do the following:

1. Place the cursor in the desired cell.
2. Enter an expression. In this case, it is "= 9/17" (since we have performed the presetting, it is not necessary to multiply by 100).
3. Press ENTER.

After that, a percentage value will appear in the cell, which means that everything has been done correctly. Now you know, but this is not all that I would like to tell in the article.

We display the percentage from the table value

It is stupid to enter the necessary values ​​for calculating the percentage yourself if you already have a table in Excel, where it would be wiser to use the program's features that will speed up this process.

So, to find out the percentage of the total, using the data in the table, you need:

1. Place the cursor in the desired place (it is better to put it next to the number, the percentage of which you want to know).
2. Enter an equal sign (=).
3. Left-click (LMB) on the number.
4. Use a fraction sign (/).
5. LMB click on the total amount.
6. Press the ENTER key.

After that, the percentage of the amount will be calculated in the place where you entered the formula. Now you know the second method, how to calculate percentages in Excel.

Fast calculation of all data

Calculating percentages in Excel can be greatly accelerated if you need to calculate many rows in one column. To do this, you need to do the following:

1. Enter the formula in the desired cell, in the same way as described in the previous section.
2. Before the second value that comes after the division sign (/), put a symbol ($), then press the right arrow and put this sign again, the result should look something like this: "= E3 / $ E $ 8".
3. Press the ENTER key to perform the calculation.
4. Stretch the selection along the entire length of the column with values ​​by holding LMB the lower right corner of the selection.

After that, all calculations will be made. As you can see, it is very convenient and much faster than entering all the formulas manually.

In this article, I am going to tell you about a very simple and common task in excel - how to add percentages in excel using formula.

The mathematical way to add percent to a number

While all computer programs are designed for humans to make their work easier, this does not mean that you don’t need to know math. Therefore, let's remember a little math, how it should be done on a piece of paper. For example, we need to add 5%. To do this, we must first find 5% of the number, and therefore add the resulting 5% to the known number. The easiest way to find 5% is to solve the proportion:

Theory is the main proportion for finding the right percentage

After finding X, the number is added to the resulting value. I would like to note right away that for many, this method will turn out to be an insurmountable pit. In the end, it looks like this:

Theory is the result of finding the right percentage

Having mastered the routine theory of mathematics, you can proceed to solving the problem in excel.

Adding percentages to a number in excel

Suppose we need to add 5% to the number 37. To do this, we will write down the number 37 in cell B2, and we will write down the formula known to us in cell C2:

After we press "enter" we can get the "wrong" result.

This result is due to the fact that the computer has incorrectly determined the format of the cell. Therefore, to obtain reliable data, you need to Specify the format of the cell with the formula "numeric".

Applying Percentage Adding to a Number

The described task is very common. For example, in economics, you need to add 3% to the wages of the brigade workers.

As you can see from the figure, cell D9 contains the formula we know. Press "enter", correct the cell format to "Numeric" (if necessary). After that, we will stretch the formula to the rest of the cells in the column. As a result, we get the following result.

The previous methods of adding percentages in Excel can be called "clumsy" and are used more often by those who have little practice with the application. This is due to the fact that there is the recommended way to add percentages is to use the percent symbol (%).

Let's say we have a small plate, in the first column we have a number, and in the second, how many percent need to be added. We will write the result in the third column.

In the third column, we need to write down the formula H3 + H3 * I3%, I draw your attention to the fact that we multiply cell H3 by cell I3%. Hope everyone saw the percent symbol.

In fact, this method is no different from what was described earlier. The developers just thought it would be intuitive.