How to calculate percentages in Excel. How to add a percentage to a number in Excel
A percentage is one hundredth of a number taken as a whole. Percentages are used to indicate the relationship of a part to the whole, as well as to compare quantities.
1% = 1 100 = 0,01
The interest calculator allows you to perform the following operations:
Find the percentage of a number
To find the percentage p from a number, you need to multiply this number by a fraction p 100
Let's find 12% of the number 300:
300 12
100
= 300 · 0.12 = 36
12% of 300 is 36.
For example, a product costs 500 rubles and there is a 7% discount on it. Let's find the absolute value of the discount:
500 7
100
= 500 · 0.07 = 35
Thus, the discount is 35 rubles.
What percentage is one number of another?
To calculate the percentage of numbers, you need to divide one number by another and multiply by 100%.
Let's calculate what percentage the number 12 is from the number 30:
12
30
· 100 = 0.4 · 100 = 40%
The number 12 is 40% of the number 30.
For example, a book contains 340 pages. Vasya read 200 pages. Let's calculate what percentage of the entire book Vasya read.
200
340
· 100% = 0.59 · 100 = 59%
Thus, Vasya read 59% of the entire book.
Add percentage to number
To add to a number p percent, you need to multiply this number by (1 + p 100)
Add 30% to the number 200:
200 (1 + 30
100
) = 200 1.3 = 260
200 + 30% equals 260.
For example, a swimming pool subscription costs 1000 rubles. Starting next month they promised to raise the price by 20%. Let's calculate how much a subscription will cost.
1000 (1 + 20
100
) = 1000 1.2 = 1200
Thus, the subscription will cost 1200 rubles.
Subtract the percentage from the number
To subtract from a number p percent, you need to multiply this number by (1 - p 100)
Subtract 30% from the number 200:
200 · (1 - 30
100
) = 200 · 0.7 = 140
200 - 30% equals 140.
For example, a bicycle costs 30,000 rubles. The store gave it a 5% discount. Let's calculate how much the bike will cost taking into account the discount.
30000 · (1 - 5
100
) = 30000 0.95 = 28500
Thus, the bike will cost 28,500 rubles.
What percentage is one number greater than another?
To calculate how many percent one number is greater than another, you need to divide the first number by the second, multiply the result by 100 and subtract 100.
Let's calculate what percentage is the number 20 more number 5:
20
5
· 100 - 100 = 4 · 100 - 100 = 400 - 100 = 300%
The number 20 is 300% greater than the number 5.
For example, the boss’s salary is 50,000 rubles, and the employee’s salary is 30,000 rubles. Let's find out how many percent the boss's salary is greater:
50000
35000
· 100 - 100 = 1.43 * 100 - 100 = 143 - 100 = 43%
Thus, the boss's salary is 43% higher than the employee's salary.
What percentage is one number less than another?
To calculate how many percent one number is less than another, you need to subtract from 100 the ratio of the first number to the second, multiplied by 100.
Let's calculate what percentage is the number 5 less number 20:
100 - 5
20
· 100 = 100 - 0.25 · 100 = 100 - 25 = 75%
The number 5 is 75% less than the number 20.
For example, freelancer Oleg completed orders worth 40,000 rubles in January, and 30,000 rubles in February. Let's find how many percent less Oleg earned in February than in January:
100 - 30000
40000
· 100 = 100 - 0.75 * 100 = 100 - 75 = 25%
Thus, in February Oleg earned 25% less than in January.
Find 100 percent
If the number x This p percent, then you can find 100 percent by multiplying the number x on 100p
Let's find 100% if 25% is 7:
7 · 100
25
= 7 4 = 28
If 25% equals 7, then 100% equals 28.
For example, Katya copies photos from her camera to her computer. In 5 minutes, 20% of the photos were copied. Let's find how long the copying process takes:
5 · 100
20
= 5 5 = 25
We find that the process of copying all photos takes 30 minutes.
IN various types activity requires the ability to calculate percentages. Understand how they are “produced.” Trading premiums, VAT, discounts, returns on deposits, securities and even tips - all this is calculated as some part of the whole.
Let's figure out how to work with percentages in Excel. A program that performs calculations automatically and allows variations of the same formula.
Working with percentages in Excel
Calculating the percentage of a number, adding and subtracting percentages on a modern calculator is not difficult. The main condition is that there must be a corresponding icon (%) on the keyboard. And then it’s a matter of technique and care.
For example, 25 + 5%. To find the meaning of an expression, you need to type this sequence of numbers and symbols on the calculator. The result is 26.25. You don't need a lot of intelligence with this technique.
To create formulas in Excel, let’s remember the school basics:
A percentage is a hundredth part of a whole.
To find a percentage of a whole number, you need to divide the desired fraction by the whole number and multiply the total by 100.
Example. 30 units of goods were delivered. On the first day we sold 5 units. What percent of the goods were sold?
5 is part. 30 is a whole. Substitute the data into the formula:
(5/30) * 100 = 16,7%To add a percentage to a number in Excel (25 + 5%), you must first find 5% of 25. At school they made up the proportion:
X = (25 * 5) /100 = 1.25
After this you can perform addition.
Once basic computational skills are restored, formulas will be easy to understand.
How to calculate percentage of a number in Excel
There are several ways.
Let's adapt the mathematical formula to the program: (part / whole) * 100.
Look closely at the formula bar and the result. The result turned out to be correct. But we not multiplied by 100. Why?
In Excel, the cell format changes. For C1 we assigned the “Percentage” format. It involves multiplying the value by 100 and displaying it on the screen with a % sign. If necessary, you can set a certain number of decimal places.
Now let’s calculate how much 5% of 25 will be. To do this, enter the calculation formula in the cell: =(25*5)/100. Result:
Or: =(25/100)*5. The result will be the same.
Let's solve the example in a different way, using the % sign on the keyboard:
Let's apply the acquired knowledge in practice.
The cost of the goods and the VAT rate (18%) are known. You need to calculate the amount of VAT.
Let's multiply the cost of the product by 18%. Let’s “multiply” the formula by the entire column. To do this, click on the lower right corner of the cell with the mouse and drag it down.
The VAT amount and rate are known. Let's find the cost of the goods.
Calculation formula: =(B1*100)/18. Result:
The quantity of goods sold, individually and in total, is known. It is necessary to find the share of sales for each unit relative to the total quantity.
The calculation formula remains the same: part/whole * 100. Only in this example we will make the reference to the cell in the denominator of the fraction absolute. Use the $ sign before the row name and column name: $B$7.
How to add a percentage to a number
The problem is solved in two steps:
And here we have done the actual addition. Let's omit the intermediate action. Initial data:
VAT rate is 18%. We need to find the VAT amount and add it to the price of the product. Formula: price + (price * 18%).
Don't forget about parentheses! With their help we establish the calculation procedure.
To subtract a percentage from a number in Excel, you should follow the same procedure. Only instead of addition we perform subtraction.
How to calculate percentage difference in Excel?
The percentage change between the two values.
First, let's abstract from Excel. A month ago, tables were brought to the store at a price of 100 rubles per unit. Today the purchase price is 150 rubles.
Percentage difference = (new data - old data) / old data * 100%.
In our example, the purchase price per unit of goods increased by 50%.
Let's calculate the difference in percentage between the data in two columns:
Don’t forget to set the “Percentage” cell format.
Let's calculate the percentage change between the lines:
The formula is: (next value – previous value) / previous value.
With this arrangement of data, we skip the first line!
If you need to compare data for all months with January, for example, we use an absolute reference to the cell with the desired value ($ sign).
How to make a chart with percentages
The first option: make a column in the table with data. Then use this data to build a chart. Select the cells with percentages and copy them - click “Insert” - select the chart type - OK.
The second option is to set the format of data signatures as a fraction. In May – 22 work shifts. You need to calculate as a percentage: how much each worker worked. We draw up a table where the first column is the number of working days, the second is the number of weekends.
Let's make a pie chart. Select the data in two columns - copy - "Paste" - chart - type - OK. Then we insert the data. Right-click on them - “Data Signature Format”.
Select "Shares". On the “Number” tab - percentage format. It turns out like this:
We'll leave it at that. And you can edit to your taste: change the color, type of diagram, make underlines, etc.
In life, sooner or later everyone will be faced with a situation where it will be necessary to work with percentages. But, unfortunately, most people are not prepared for such situations. And this action causes difficulties. This article will tell you how to subtract percentages from a number. Moreover, they will be dismantled various ways solving a problem: from the simplest (using programs) to one of the most complex (using a pen and paper).
We subtract manually
Now we will learn how to subtract with a pen and paper. The actions that will be presented below are studied by absolutely every person in school. But for some reason, not everyone remembered all the manipulations. So, we have already figured out what you will need. Now we'll tell you what needs to be done. To make it more clear, we will consider an example, taking specific numbers as a basis. Let's say you want to subtract 10 percent from the number 1000. Of course, it is quite possible to do these actions in your head, since the task is very simple. The main thing is to understand the very essence of the decision.
First of all, you need to write down the proportion. Let's say you have two columns with two rows. You need to remember one thing: numbers are entered in the left column, and percentages are entered in the right column. Two values will be written in the left column - 1000 and X. X is included because it symbolizes the number that will need to be found. In the right column will be entered - 100% and 10%.
Now it turns out that 100% is the number 1000, and 10% is X. To find x, you need to multiply 1000 by 10. Divide the resulting value by 100. Remember: the required percentage must always be multiplied by the taken number, after which the product should be divided by 100%. The formula looks like this: (1000*10)/100. The picture will clearly show all the formulas for working with percentages.
We got the number 100. This is what lies under that X. Now all that remains to be done is subtract 100 from 1000. It turns out 900. That's all. Now you know how to subtract percentages from a number using a pen and notebook. Practice on your own. And over time, you will be able to perform these actions in your mind. Well, we move on, talking about other methods.
Subtract using the Windows calculator
It’s clear: if you have a computer at hand, then few people will want to use a pen and notebook for calculations. It's easier to use technology. That’s why we’ll now look at how to subtract percentages from a number using a Windows calculator. However, it is worth making a small note: many calculators are capable of performing these actions. But the example will be shown using a Windows calculator for greater understanding.
Everything is simple here. And it is very strange that few people know how to subtract percentages from a number in a calculator. Initially, open the program itself. To do this, go to the Start menu. Next, select "All Programs", then go to the "Accessories" folder and select "Calculator".
Now everything is ready to start solving. We will operate with the same numbers. We have 1000. And we need to subtract 10% from it. All you need to do is enter the first number (1000) into the calculator, then press minus (-), and then click on percentage (%). Once you have done this, you will immediately see the expression 1000-100. That is, the calculator automatically calculated how much 10% of 1000 is.
Now press Enter or equals (=). Answer: 900. As you can see, both the first and second methods led to the same result. Therefore, it’s up to you to decide which method to use. Well, in the meantime we move on to the third and final option.
Subtract in Excel
Many people use Excel. And there are situations when it is vital to quickly make calculations in this program. That is why now we will figure out how to subtract a percentage from a number in Excel. This is very easy to do in the program using formulas. For example, you have a column with values. And you need to subtract 25% from them. To do this, select the column next to it and enter equal (=) in the formula field. After that, click LMB on the cell with the number, then put “-” (and again click on the cell with the number, after that enter - “*25%). You should get something like in the picture.
As you can see, this is the same formula as given the first time. After pressing Enter you will receive the answer. To quickly subtract 25% from all the numbers in a column, just hover over the answer, placing it in the lower right corner, and drag down to required quantity cells. Now you know how to subtract a percentage from a number in Excel.
Conclusion
Finally, I would like to say only one thing: as you can see from all of the above, in all cases only one formula is used - (x*y)/100. And it was with her help that we were able to solve the problem in all three ways.
Instructions
Let's say you need to add a fixed percentage to the original number placed in cell A1 and display the result in cell A2. Then a formula should be placed in A2 that increases the value from A1 by a certain factor. Start by determining the value of the multiplier - add one hundredth of a fixed percentage to one. For example, if you need to add 25% to the number in cell A1, then the multiplier will be 1 + (25/100) = 1.25. Click A2 and type the desired formula: enter the equal sign, click cell A1, click (multiplication sign) and type the factor. The entire entry for the example above should look like this: =A1*1.25. Press Enter and Excel will calculate and display the result.
If you need to calculate the same percentage of the values in each cell of a certain column and add the resulting value to the same cells, then place the multiplier in a separate cell. Let's say you need to add 15%, and the original values occupy the first to twentieth lines in column A. Then place the value 1.15 (1 + 15/100) in a free cell and copy it (Ctrl + C). Then select the range from A1 to A20 and press the keyboard shortcut Ctrl + Alt + V. The Paste Special dialog will appear on the screen. In the “Operation” section, check the box next to “multiply” and click OK. After this, all selected cells will change by the specified percentage value, and the auxiliary cell containing the multiplier can be deleted.
If it is necessary to be able to change the added percentage, it is better to place it in a separate cell, rather than correct it every time. For example, place the original value in the first line of the first column (A1), and the percentage that needs to be added in the second column of the same line (B1). To display the result in cell C1, enter the following formula into it: =A1*(1+B1/100). After pressing the Enter key, the required value will appear in the column if cells A1 and B1 already contain required values.
Sources:
- how to add cells in excel
- Solution complex tasks on interest
In a Microsoft Office application, you can perform the same action in several ways. It all depends on what will be more convenient for the user. To add a cell to a sheet, you can choose the method that suits you.
Instructions
To add a new cell, place the cursor in the one above which you plan to add another one. Open the “Home” tab and click on the “Insert” button in the “ Cells" If you select several cells horizontally and press the same button, the same number of cells will be added above as were selected. If you select them vertically, new cells will be added to the left of the selected range.
To more accurately indicate where the additional cell should be located, place the cursor in the one near which you want to add a new one and right-click in it. In the context menu, select the second one from the top of the two “Insert” commands. A new dialog box will appear. Place a marker in it opposite one of the options: “ Cells, shifted to the right" or " Cells, with a downward shift." Click on the OK button. If you select several cells at once, the same number of new ones will be added.
The same actions can be performed using the buttons on the toolbar. Select one or more cells and click under " Cells» not the “Insert” button itself, but the arrow-shaped button located next to it. A context menu will open, select “Insert Cells” from it. After this, the same window that was discussed in the second step will appear. Select the option for adding cells that suits you and click OK.
If you designed your table using the Table tool, you will only be able to add new rows or columns. To be able to insert an empty cell, right-click in the table, select the “Table” item in the context menu and the “Convert to range” sub-item. Confirm your actions in the request window. The table view will change, after which you can insert a cell using one of the methods described above.
In this article I will tell you about a very simple and common task in Excel - how to add percentages in excel using formula.
Mathematical way to add percentages to a number
Although everything computer programs are developed for people to make their work easier, this does not mean that you don’t need to know mathematics. So let's remember a little mathematics, how to do this 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 simplest way finding 5% is solving the proportion:
Theory - the basic proportion for finding the desired percentage
After finding X, the number is added with the resulting value. I would like to immediately note that for many, such a method will turn out to be an insurmountable pitfall. In the end it looks like this:
Theory is the result of finding the desired percentage
Having mastered the routine theory of mathematics, you can move on 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 the number 37 in cell B2, and in cell C2 we will write the formula we know:
After we press "enter" we may get the "wrong" result.
Obtaining such a result is due to the fact that the computer incorrectly determined the cell format. Therefore, to obtain reliable data, you need to specify the cell format with the “numeric” formula.
Applying percentage addition to a number
The described task occurs very often. For example, in economics, you need to add 3% to wages brigade workers.
As can be seen from the figure, cell D9 contains the formula known to us. Press “enter” and change the cell format to “Numeric” (if necessary). After this, we will extend the formula to the remaining cells in the column. As a result, we will 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 percentage symbol (%).
Let's say we have a small tablet, in the first column we have a number written, and in the second, how many percentages need to be added. We will write the result in the third column.
In the third column we need to write the formula H3+H3*I3%, please note that we multiply cell H3 by cell I3%. I hope everyone saw the percentage symbol.
In fact, this method no different from what was described earlier. The developers just thought it would be intuitive.