Explanation
In this example, the goal is to calculate the variance between a Forecast (column C) and Actual (column D) as a percentage. For example, with a Forecast value of 100,000 and an Actual value of 112,000, we want to return a variance of 12%.
The concept of variance requires a baseline value and a “new” value. The baseline value is subtracted from the new value and the result is divided by the baseline value. The general formula, where “x” is the variance, is:
x=(new-baseline)/baseline
x=(112,000-100,000)/100,000
x=12,000/100,000
x=0.12
After converting to an Excel formula with cell references, the formula in E5, copied down, is:
=(D5-C5)/C5
=(112,000-100,000)/100,000
=12,000/100,000
=0.12
=12%
As the formula is copied down, it returns a decimal number for each item in the list. When these numbers are formatted with the Percentage number format , they are displayed as percentages.
Formatting percentages in Excel
In mathematics, a percentage is a number expressed as a fraction of 100. For example, 25% is read as “Twenty-five percent” and is equivalent to 25/100 or 0.25. Accordingly, the values in column E are decimal values , with the Percentage number format applied. To convert these values to a whole number like 12, multiply by 100:
=(D5-C5)/C5*100
Negative numbers
If you have a negative value for the original number, the above formula won’t work and can be adjusted by adding the ABS function :
=(new-original)/ABS(original)
ABS stands for “absolute value”, and it converts negative values to positive values. In this case, the ABS function ensures the original value is positive when the variance is calculated.
Note: be aware that results negative values can be misleading, as explained by Jon Acampora in his detailed article on the topic .
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.
