Calculating percentages – actually a simple thing, but calculating percentages always gives some people a headache. Good, there is Excel, because who knows the right formulas, solves the problem in the twinkling of an eye. In this article you will find five useful formulas for different percentage calculations with Excel.
Actually a simple thing, but calculating percentages always gives some people a headache. Good thing Excel exists, because if you know the right formulas, you can solve the problem in no time at all. Here are five useful formulas for different percentage calculations using Excel.
Percent of an amount
Amount multiplied by percentage, this is the formula in words. It is important that you enter the percentage right away with the percent sign in the cell. 100% corresponds to the number 1. The result of 50% of 400 is 200, so the calculation =400*50% is the same as =400*0.5. As soon as you write a number with a percentage, the number is divided by 100 in the background. 8% equals 0.08, 2.5% on the other hand 0.025.
Formula: =AMOUNT*PERCENTAGE
Calculate net amount
When an amount z. B. already contains VAT of 8% and you want to find out what the amount would be without VAT, then the calculation would be as follows:
Formula: =AMOUNT/(1+PERCENTAGE)
This variant has the advantage that it reacts dynamically to changes in the percentage, while the first one remains static with respect to the divisor.
If you have entered the percentage without a percent sign, the formula is as follows:
formula: =AMOUNT/1+(PERCENTAGE/100)
Add percentage amount
In the previous example, we would now add the amount with the result of the percentage calculation.
However, this calculation can also be done in one step.
Formula: = AMOUNT+AMOUNT*PERCENTAGE
For a better overview, you can also use brackets again:
Formula: = AMOUNT+(AMOUNT*PERCENTAGE)
Exactly the same procedure, if you want to subtract the percentage amount, then simply subtract instead of adding.
formula: =AMOUNT-AMOUNT*PERCENTAGE
Percentage
A percentage provides information about the ratio of an amount to the total amount.
Formula: =AMOUNT/TOTAL
The amount still needs to be formatted with percent. Add extra decimal places to get a more accurate figure.
The result:
Calculate total amount from percentage
If you want to make the reverse calculation, i.e. calculate the total amount from the share, divide the share by the percentage.
Formula: =PROPORTION/PERCENTAGE
Excel courses at Digicomp
Learn the basics in our courses or deepen your knowledge in using Microsoft Excel.
Learn the basics in our courses or deepen your knowledge in the use of Microsoft Excel.