How to Calculate Percentage Variance (Difference) in Excel

Calculating percentage variance or difference in Excel can be very useful in analyzing data and trends. It can help you understand how much a value has changed over time or how different it is from an expected value. In this blog post, we will learn how to calculate percentage variance

What is Percentage Change?

Percentage change is a measure of how much a value has increased or decreased over time. It is often used in analyzing data and trends, and can help you understand the magnitude and direction of a change.

Percentage change is calculated by dividing the difference between two values by the original value, and then multiplying the result by 100 to express it as a percentage.

A positive percentage change indicates an increase, while a negative percentage change indicates a decrease.

How to Calculate Percentage Variance (Difference) in Excel

Now, let’s dive into the step-by-step process of calculating percentage variance in Excel:

Step 1. Enter the old value in one cell, let’s say it’s in cell A1.

Step 2. Enter the new value in another cell, let’s say it’s in cell B1.

Step 3. In a third cell, enter the formula:

=(B1-A1)/A1

Step 4. Press Enter, and the result will be displayed as a decimal value.

Step 5. To display the result as a percentage, multiply the decimal value by 100 and format the cell as a percentage.

By applying the Percentage format in Excel, you can skip the last step of multiplying the decimal result by 100 to convert it to a percentage.

Here’s a more straightforward formula for calculating percentage change in Excel, which yields the same result.

=B1/A1-1

This formula is used to calculate the percentage change between two values.

Here’s what each part of the formula represents

• B1 is the new value or the value after the change.
• A1 is the old value or the value before the change.

To calculate the percentage change between these two values, we first subtract the old value from the new value, which gives us the difference between the two values.

Then we divide this difference by the old value, which gives us the ratio of the change relative to the old value. This ratio is typically expressed as a decimal.

Finally, we can multiply this decimal by 100 to express it as a percentage, or we can apply the Percentage format to the cell to achieve the same result.

So, the formula “(B1-A1)/A1” essentially gives us the percentage change between the two values, which can be positive (indicating an increase) or negative (indicating a decrease).

Excel Percent Change Absolute Value

The ABS function in Excel is used to return the absolute value of a number. Which is the value without its sign. This means that if the number is positive. The absolute value will be the same as the original number, while if the number is negative, the absolute value will be the positive equivalent.

To use the ABS function to find the percentage change in Excel, we can modify the original formula slightly by wrapping it in the ABS function. The formula would look like this:

=ABS((new_value - old_value) / old_value)

This formula will give us the absolute value of the percentage change between the old and new values. The result will always be positive, regardless of whether the change is an increase or decrease.

For example, if the old value is £100 and the new value is £80, the original formula would give us a result of -0.2 or -20%, indicating a 20% decrease.

If we wrap this formula in the ABS function, we get the result 0.2 or 20%, which is again the absolute value of the percentage change.

Calculate Discount Percentage

This formula can be used to calculate the percentage discount given on an item.

=(discounted price - regular price) / regular price

For example, let’s say you are purchasing a pair of shoes that are normally priced at £50, but are on sale for £40. To calculate the percentage discount, we can use the formula as follows:

=(B2-A2)/A2

So the discount on the shoes is 20%. Which means you will pay £40 for a product that is normally priced at £50.

Note that the result of the formula will be negative if the discounted price is less than the regular price.

This indicates a discount, but the negative sign can be confusing.

To avoid this, we can use the ABS function to get the absolute value of the result. This will give us the percentage discount without the negative sign.

Calculate the Value After Percent Change

Let’s say we have an initial value of £12 and the value increases by 20%. We can use the formula to calculate the new value as follows:

• In cell A2, enter the original price.
• In cell B2, enter the percentage change as a decimal.

For example, if you want to increase the price by 20%, enter “0.2” in cell B2.

If you want to decrease the price by 10%, enter “-0.1” in cell B2.

In cell C2, enter the formula to calculate the new price using the following formula:

=A2*(1+B2)

The formula A2*(1+B2) multiplies the original price in cell A2 by the sum of 1 and the percentage change in cell B2.

This calculates the new price after the percentage change, and displays it in cell C2.