Formula in excel number minus percent. All methods of calculating interest in Microsoft Excel - formulas and examples of use

Hi all! Do you know how to calculate percentages in Excel? In fact, interest in life accompanies us very often. After today's lesson, you will be able to calculate the profitability of an idea that has suddenly arisen, find out how much you actually get by participating in store promotions. Master some points and be with percentages on "You".

I'll show you how to use the basic calculation formula, calculate percentage growth, and other tricks.

Such a definition as a percentage is familiar to everyone from the school bench. It comes from Latin and literally means “out of a hundred”. There is a formula that calculates percentages:

Consider an example: there are 20 apples, 5 of which you treated your friends. Determine in percentage, what part did you give away? Through simple calculations, we get the result:

In this way, interest is calculated both at school and in everyday life. Thanks to Excel, such calculations become even easier, because everything happens automatically. There is no single formula for calculations. The choice of calculation method will depend on the desired result.

How to calculate percentages in Excel: the basic calculation formula

There is a basic formula that looks like:
Unlike school calculations, this formula does not need to be multiplied by 100. Excel takes care of this moment, provided that the cells are assigned a certain percentage format.

Let's consider a specific situation. There are products that are in the order stage, and there are goods that have been delivered. The order in column B, named Ordered. Delivered products are listed in column C named Delivered. It is necessary to determine the percentage of the delivered fruit:

• In cell D2, write the formula =C2/B2. Determine how many lines you need and use autocomplete to copy it.
• In the "Home" tab, find the "Number" command, select "Percent Style".
• Look at the decimal places. Adjust their number if necessary.
• Everything! Let's see the result.

The last column, D, now contains values ​​that represent delivered orders as a percentage.

How to calculate percentage of a total in excel

We will now look at some more examples of how to calculate percentage in excel of the total amount, which will allow you to better understand and assimilate the material.

1. The calculated amount is located at the bottom of the table

At the end of the table, you can often see the "Total" cell, where the total is located. We have to count each part to the final value. The formula will look like in the previously considered example, but the denominator of the fraction will contain an absolute reference. The $ sign will be in front of the row and column names.

Column B is filled with values, and cell B10 contains their total. The formula will look like:
Using a relative link in cell B2 will allow it to be copied and pasted into cells in the Ordered column.

2.Parts of the sum are located on different lines

Suppose we need to collect data that is in different rows and find out what part is occupied by orders for a particular product. Adding specific values ​​is possible using the SUMIF (SUMIF) function. The result that we get will be necessary for us to calculate the percentage of the amount.

Column A is our range, and the summation range is in column B. We enter the name of the product in cell E1. This is our main criterion. Cell B10 contains the sum of the products. The formula looks like this:

In the formula itself, you can place the name of the product:

If you need to calculate, for example, how much cherries and apples take as a percentage, then the amount for each fruit will be divided by the total. The formula looks like this:

Calculating percent change

Calculating data that changes can be expressed as a percentage and is the most common task in Excel. The formula for calculating the percentage change is as follows:

In the process of work, you need to accurately determine which of the values ​​which letter occupies. If you have more of any product today, this will be an increase, and if less, then a decrease. The following scheme works:

Now we need to figure out how we can apply it in real calculations.

1. Count the changes of two columns

Let's say we have 2 columns B and C. In the first one, we display the prices of the last month, and in the second - this month. To calculate the resulting changes, we enter a formula in column D.

The results of the calculation using this formula will show us the presence of an increase or decrease in price. Fill in the formula with all the lines you need using autocomplete. For cells with a formula, be sure to activate the percentage format. If you did everything correctly, you get such a table, where the increase is highlighted in black, and the decrease is in red.

If you are interested in changes over a certain period and the data is in one column, then we use the following formula:

We write down the formula, fill it with all the lines that we need and get the following table:

If you want to count the changes for individual cells, and compare them all with one, use the absolute reference we already know using the $ sign. We take January as the main month and calculate the changes for all months as a percentage:

Copying the formula over other cells will not change it, but a relative reference will change the numbering.

Calculate the value and the total amount from a known percentage

I clearly demonstrated to you that there is nothing complicated in calculating percentages through Excel, just like in calculating the amount and values ​​​​when the percentage is already known.
1. Calculation of a value from a known percentage

For example, you buy a new phone that costs $950. You are aware of the 11% VAT surcharge. It is required to determine the surcharge in monetary terms. This formula will help us with this:

In our case, applying the formula =A2*B2 gives the following result:

You can take as a decimal value, and using a percentage.

2. Calculation of the total amount

Consider the following example where the known starting amount is $400 and the salesperson tells you that the price is now 30% less than last year. How can I find out the original cost?

The price decreased by 30%, which means that we need to subtract this indicator from 100% to determine the required share:

The formula that determines the initial cost is:

Given our task, we get:

Converting a value to a percentage

This method of calculation is useful for those who are especially careful about their expenses and want to make some changes to them.

To increase the value by a percentage, we will use the formula:

We need to reduce the percentage. Let's use the formula:

Using the formula =A1*(1-20%) reduces the value that is contained in the cell.

Our example shows a table with columns A2 and B2, where the first is the current expenses, and the second is the percentage by which you want to change the expenses in one direction or another. Cell C2 should be filled with the formula:

Increment by percentage of values ​​in a column

If you want to make changes to the entire data column without creating new columns for this and using an existing one, you need to take 5 steps:

Now we see values ​​increased by 20%.

Using this method, you can perform various operations for a certain percentage by entering it into a free cell.

Today was an extensive lesson. I hope you made it clear to yourself how to calculate percentage in excel. And, despite the fact that such calculations are not very popular for many, you will do them with ease.

For five years, calculate the tax on the sale of goods or find out how much it will cost to take a loan. In any of these cases, you will have to deal with interest. The calculator is a good thing, but it cannot give clarity and recalculate the result when the source data changes, just as Excel easily does. In addition, calculations can always be saved in a file (it is clear that the calculator does not have such functionality).

When interest is calculated in Excel, the calculation formula is selected depending on what data is known and what specifically needs to be determined. As we know from school, the basic formula can be written like this: percentage = value / grand total.

Calculation of the value of the percentage of the total

For example, if you win 5,000 rubles in the state lottery, you need to pay 15% How much will you need to pay to the state?

Calculating percentages given a known value and a grand total

For example, if there are 29 students in a class, of which 16 are boys, what is the percentage of boys?

If you use the Percent Format button (Home tab, Number group), you can present the total as a percentage.

Calculating a Grand Total Using a Value and a Percentage

For example, in a store, jeans cost 2390 rubles. This week there is a 23% discount in honor of February 23rd. How much will jeans cost next week?

Calculating percentages as the difference of two numbers

For example, the salary in February was 24,780 rubles, and in March - 25. How many percent did income increase over this period? To solve this example, you need to apply the ABC function in addition to the basic division and subtraction operators.

Note. The ABC function is designed to determine the absolute value of a number. In other words, it returns the modulus, which is an unsigned number.

Decrease or increase a number by a specified percentage

For example, the rate of ordinary securities of Sberbank over the past week increased by 1.521% and amounted to 97.96 rubles per share. What was the original value of the papers? Or, conversely, Sberbank is expected to rise in a couple of days (at least 2%). What will be their final cost then? For more convenience, you can copy the contents of the following table into a new Excel sheet. If suddenly an error appears instead of the results, try replacing commas in numbers with periods.

As you probably have already seen, calculating percentages in Excel is a very simple and convenient thing. No wonder this program is so loved by accountants, bankers, financiers and just all those who often have to work with numbers.

The most convenient way to calculate and work with percentages is in Excel in the Microsoft Office suite, because all that is required is to enter the values ​​​​and the desired formula.

A percentage is a hundredth of a whole number, which on paper is indicated by the sign% or decimal fractions (70% \u003d 0.70). The standard expression for percentage calculations is Whole/Part*100, but thanks to Excel, you don't have to manually calculate anything.

How to calculate percentages in Excel

The simplicity of working with the program lies in the fact that it is enough for the user to enter the values ​​​​of the whole and its parts (or select from the previously entered data), and indicate the calculation principle, and Excel will perform the calculations on its own. In Excel, the percentage is calculated like this − Part/Whole = Percentage, and multiplication by 100 happens automatically when the user selects the percentage format:

For calculations, let's take the calculation of the implementation of the work plan:

The program will independently calculate the percentage of plan completion for each product.

Percentage of the number

In Excel, you can calculate a number, knowing only its fraction: %*Part = Integer. Let's say you need to calculate what is 7% of 70. To do this:

If the calculation is carried out when working with a table, then instead of entering numbers, you must give links to the desired cells. It is worth being careful, when calculating the format should be general.

Percentage of the amount

If the data is scattered throughout the table, then you need to use the formula SUMIF- it adds up the values ​​that meet the given parameters, in the example - the given products. An example formula would look like this - “=SUMIF (criteria range; addition range) / total amount”:

Thus, each parameter is considered, i.e. product.

Calculating Percentage Change

Comparing two shares is also possible using Excel. To do this, you can simply find the values ​​\u200b\u200band subtract them (from the larger to the smaller), or you can use the increase / decrease formula. If you need to compare the numbers A and B, then the formula looks like this: (B-A)/A = difference". Consider an example calculation in Excel:

1. stretch formula to the entire column using the autocomplete token.

If the calculated indicators are located in one column for a specific product for a long period of time, then the calculation method will change:

Positive values ​​indicate an increase, while negative values ​​indicate a decrease.

Calculation of value and total

Often, it is necessary to determine the total amount knowing only the share. In Excel, this can be done in two ways. Consider buying a laptop assuming it costs $950. The seller says that this price does not include VAT, which is 11%. The final margin can be found by making calculations in Excel:

Let's consider the second calculation method using another example. Let's say that when buying a laptop for $400, the seller says that the price is calculated taking into account a 30% discount. You can find out the starting price like this:

The initial cost will be 571.43 dollars.

How to change value to percentage value

Often you have to increase or decrease the final number by some fraction of it, for example, you need to increase your monthly costs by 20%, 30% and 35%:

The program will calculate the total on its own for the entire column, if you stretch the expression using the fill marker. The expression for reducing the amount is the same, only with a minus sign - " =Value*(1-%)».

Operations with interest

With shares, you can perform the same operations as with ordinary numbers: addition, subtraction, multiplication. For example, in Excel, you can calculate the difference in performance between a company's sales using the ABS command, which returns the absolute value of a number:

The difference between the indicator will be 23%.

You can also add (or subtract) a percentage to a number - consider the action using the example of planning a vacation:

The program will independently perform the calculations and the results will be 26,000 rubles for the vacation week and 14,000 rubles after the vacation, respectively.

Multiplying a number by a fraction is much easier in Excel than manually, since it is enough to specify the required value and percentage, and the program will calculate everything itself:

All amounts can be quickly recalculated by stretching the formula over the entire column F.

To subtract a share, you must enter a number in cell A1, and a percentage in cell A2. Make calculations in cell B1 by entering the formula " =A1-(A1*A2)».

We will analyze various formulas for calculating interest in Excel, for this we will consider real practical examples, formulas and typical financial tasks.

Interest formula. Basic concepts

Interest(Latin. pro centum) - are an integral part of financial mathematics and are used in the banking sector, finance, accounting, insurance, taxation, etc. So, in the form of interest, the profitability and profitability of the enterprise, the rate on bank loans and loans, tax rates, etc. are expressed. One can cite A. Einstein's statement to confirm the importance of interest in the financial world: "Compound interest is the most powerful force in nature." Before considering the formulas for calculating interest, let's introduce the basic terms and concepts.

• Capital (English capital,Principal) - is the base against which the percentage is calculated.
• Interest frequency- the period of payment of interest on capital.
• Interest rate (Englishrate) is the amount of interest or share of capital to be paid.
• Investment period (Englishperiod) is the time interval for the transfer of capital to a bank or other financial institution.

So, let's consider various econometric problems with percentages.

Share formula

The calculation of the share is often necessary in accounting and financial accounting, where it is necessary to determine the share of certain types of assets in relation to the total. The figure below shows an example and accounting data for OJSC ALROSA.

Share of reserves in assets=B6/B7

Percentage calculation

In order for the received shares in the cell to have a percentage form, you can use the key combination "Ctrl" + "Shift" + "%".

The formula for calculating the percentage of capital

Consider the second recurring problem in economics: calculating the absolute value of interest on capital.

Task example. It is necessary to determine the amount of payment by the bank of Alfa-Bank OJSC on a deposit in the amount of 100,000 rubles. with an interest rate of 15%. The formula for calculating the amount of payments will be as follows:

Bank deposit payments=B6*B7

Calculation of payments on a bank deposit in Excel

The formula for calculating the amount of capital with interest

Task example. It is required to calculate the final cost of goods with VAT (value added tax), while the cost of goods without VAT is 10,000 rubles, the tax percentage is 18%. The formula for calculating the cost of goods, including VAT, is calculated by the formula:

The cost of goods including VAT=B6*(1+B7)

Calculation of the cost including VAT in Excel

The formula for reducing the amount of capital by a percentage

Remaining capital=B6-B6*B7

Calculation of the cost of capital after interest

Formula for calculating the cleared cost of capital (production)

The cost of products without VAT=B6/(1+B7)

An example of calculating the cleared cost of products without VAT in Excel

The formula for calculating simple interest on a bank deposit

When using simple interest, payments on the deposit are made only at the end of the term (period) of placement.

Task example. It is required to calculate the amount of payments on a bank deposit, on which accrued simple interest. The amount of the deposit is 150,000 rubles, the annual interest rate on the deposit is 12% (for 365 days), the deposit period is 300 days. The calculation formula in Excel is as follows:

Amount of bank deposit at the end of the placement period=(1+B8*300/365)*B6

An example of calculating simple interest on a bank deposit in Excel

The formula for calculating compound interest on a bank deposit

Compound interest differs from simple interest in that payments to a bank deposit are made during the period of its placement.

Task example. It is necessary to calculate the size of the bank deposit, which was placed at compound interest. The initial deposit amount is 100,000 rubles, the annual interest rate is 14%, the interest accrual period is every 4 months, the deposit period is 1 year.

Formula for calculating compound interest:

The amount of the deposit with compound interest at the end of the year=B6*(1+B8*B9/B7)^4

An example of calculating compound interest in Excel

Video lesson: "Examples of calculating simple and compound interest"

Summary

Interest calculation is an integral part of financial mathematics and the whole economy as a whole. The quick interest calculation skill in Excel saves time when estimating the future cost of capital.

And again we return to native Excel. This program never ceases to amaze with its practicality and usefulness. Today, let's see how percentages are calculated in Excel.

How to calculate percentages in Excel?

Here's what to do to calculate percentages in Excel:

• Select the cell in which you want to calculate percentages.
• In this cell, enter an exactly sign, a number or the address of the cell containing the desired value, the multiply sign, the percentage number, and the percent sign.
• Now you need to press Enter.
• You will get the right answer in the cell if you entered everything correctly.

Symbol notation:

"equal" - "="

"multiply" - "*"

"percentage" - "%"

The percentage may already be in some other Excel cell. This allows you to calculate percentages in Excel quickly. To do this, you need to set a specific cell format. This can be done like this: "Format-cells-number-percentage". Now we need to multiply the contents of this cell by the contents of the cell containing the percentages. Get the finished value.

With Excel, you can calculate not only various percentages, formulas, and so on. The program has an impressive set of high-quality and powerful tools for organizing your data. A user-friendly interface and detailed help will help even an inexperienced accountant understand the program. Excel is distinguished by practicality, reliability, stable operation and a high level of systematization and storage of data on a computer. Moreover, the program itself will not take up much space in your computer's memory. Excel is the go-to for anyone who wants to keep their resources in order.