Explanation
In this example, the goal is to calculate various percentages of the number in cell B5. This is a straightforward calculation in Excel. The main task is to correctly enter the numbers in column D as percentages. Once that is done, you can multiply the percentage by the number.
The first step is to format the cells in the range D5:D12 with the percentage number format . You can use the keyboard shortcut Control + Shift + % . Once the cells are correctly formatted, you can enter the numbers without the % operator . Excel will automatically add the % sign.
Next, enter the formula in cell E5. This formula simply multiplies the amount in B5 by the percentage in D5:
=$B$5*D5
Because the formula will be copied down, the reference to cell B5 must be an absolute reference . This locks the reference and prevents changes as the formula is copied down. Because the reference to cell D5 is relative , it will change at each new row.
The results in column E are the amounts of B5 that correspond with each percentage in column D. If the number in B5 is changed, the results in column E are updated.
Note: the percentages in column D are completely arbitrary and can be changed as desired.
Explanation
In this example, the goal is to decrease the prices shown in column C by the percentages shown in column D. For example, given an original price of $70.00, and an decrease of 10% ($7.00), the result should be $63.00. The general formula for this calculation, where “x” is the new price, is:
x=old*(1-percentage)
x=70*(1-10%)
x=70*0.90
x=63.00
Converting this to an Excel formula with cell references, the formula in E5 becomes:
=C5*(1-D5)
=70*(1-0.1)
=70*0.9
=63.00
As the formula is copied down, the formula returns a new price for each item in the table, based on the percentages shown in column D.
Negative percentages
A negative percentage will have the effect of increasing the original price. For example, with -10% in cell D5 (-0.10), the formula evaluates like this:
=C5*(1-D5)
=70*(1--0.1)
=70*1.10
=77.00
This example explains the general formula for increasing a number by a given percentage.
Formatting percentages in Excel
In mathematics, a percentage is a number expressed as a fraction of 100. For example, 95% is read as “Ninety-five percent” and is equivalent to 95/100 or 0.95. Accordingly, the values in column D are decimal values, with the Percentage number format applied.
