Contents
Determining the percentage of a number is a fairly common task that an Ecxel user who works with numbers has to face. Such calculations are necessary to perform many tasks: determining the size of the discount, markup, taxes, and so on. Today we will learn in more detail what to do to multiply a number by a percentage.
How to multiply a number by a percentage in Excel
What is a percentage? This is a fraction of 100. Accordingly, the percent sign is easily translated into a fractional value. For example, 10 percent equals the number 0,1. Therefore, if we multiply 20 by 10% and by 0,1, we will end up with the same number – 2, since that is exactly the tenth of the number 20. There are many ways to calculate percentages in spreadsheets.
How to calculate percentage manually in one cell
This method is the easiest. It is enough to simply determine the percentage of a certain number using a standard formula. Select any cell, and write down the formula: uXNUMXd number * number of percent. This is a universal formula. How it looks in practice is easy to see in this screenshot.
We see that we have used the formula =20*10%. That is, the order of calculation is written in the formula in exactly the same way as a conventional calculator. That is why this method is so easy to learn. After we entered the formula manually, it remains to press the enter key, and the result will appear in the cell where we wrote it down.
Do not forget that the percentage is written both with the % sign and as a decimal fraction. There is no fundamental difference between these recording methods, since this is the same value.
Multiply a number in one cell by a percentage in another cell
The previous method is very easy to learn, but has one drawback – we do not use the value from the cell as the number. Therefore, let’s see how you can get percentage data from a cell. The mechanics are generally similar, but one additional action needs to be added. To do this, follow the steps described in this manual:
- Suppose we need to find out what the size of the allowance is and display it in column E. To do this, select the first cell and write the same formula in it as in the previous form, but instead of numbers, specify cell addresses. You can also act in the following sequence: first write the formula input sign =, then click on the first cell from which we want to get data, then write the multiplication sign *, and then click on the second cell. After entering, confirm the formulas by pressing the “ENTER” key.
- In the required cell, we see the total value.
To perform automatic calculations of all other values, you need to use the autocomplete marker.
To do this, move the mouse cursor to the lower left corner and drag to the end of the table column. The required data will be automatically applied.
There may be another situation. For example, we need to determine how much a quarter of the values contained in a certain column will be. Then you need to do exactly the same as in the previous example, only write 25% as the second value instead of the address of the cell containing this fraction of the number. Well, or divide by 4. The mechanics of actions are the same in this case. After pressing the Enter key, we get the final result.
This example shows how we determined the number of defects based on the fact that we know that about a quarter of all bicycles produced have defects. There is another way how it calculates the value as a percentage. To demonstrate, let’s show the following problem: there is a column C. Numbers are located in it. An important clarification – the percentage is indicated only in F2. Therefore, when transferring the formula, it should not change. What to do in this case?
In general, you need to follow the same sequence of actions as in the previous cases. First you need to select D2, put the = sign and write the formula for multiplying cell C2 by F2. But since we only have a percentage value in one cell, we need to fix it. For this, the absolute address type is used. It does not change when copying a cell from one location to another.
To change the address type to absolute, you need to click on the F2 value in the formula input line and press the F4 key. After that, a $ sign will be added to the letter and number, which means that the address has changed from relative to absolute. The final formula will look like this: $F$2 (instead of pressing F4, you can also add the $ sign to the address yourself).
After that, you need to confirm the changes by pressing the “ENTER” key. After that, the result will be visible in the first cell of the column describing the amount of marriage.
Now the formula is transferred to all other cells, but the absolute reference remains unchanged.
Choosing how to display percentage in a cell
It has been previously discussed that percentages come in two basic forms: as a decimal fraction or in the classic % form. Excel allows you to choose the one that you like best in a particular situation. To do this, you need to right-click on the cell containing a fraction of the number, and then change the cell format by selecting the appropriate item in the context menu.
Next, a window will appear with several tabs. We are interested in the very first one, signed as “Number”. There you need to find the percentage format in the list on the left. The user is shown in advance what the cell will look like after it is applied to it. In the field on the right, you can also select the number of decimal places that is allowed when displaying this number.
If you want to display a fraction of a number as a decimal fraction, you must select a number format. The percentage will automatically be divided by 100 to make a fraction. For example, a cell containing a value of 15% will automatically be converted to 0,15.
In both cases, to confirm your actions after entering data into the window, you need to press the OK button. We see that there is nothing complicated in multiplying a number by a percentage. Good luck.