The results in column E are decimal values with the percentage number format applied. The same formula can be used to calculate things like variance between this year and last year, variance between a budget and actual values, and so on. 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: After converting to an Excel formula with cell references, the formula in E5, copied down, is: 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:
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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
