Calculating Percent Variance with Negative Values in Excel Formula

What is Percent Variance?

Percent variance is a measure of how much a value has changed from a baseline or reference value. It is often used to compare the performance of a business, project, or process over time, or to evaluate the difference between actual and expected results.

The formula for calculating percent variance is:

Percent Variance = (New Value - Old Value) / Old Value

For example, if the sales revenue of a company in 2023 was $120,000 and in 2024 it was $150,000, the percent variance would be:

Percent Variance = (150,000 - 120,000) / 120,000
Percent Variance = 0.25
Percent Variance = 25%

This means that the sales revenue increased by 25% from 2023 to 2024.

What is the Problem with Negative Values?

The formula for calculating percent variance works well when both the new value and the old value are positive numbers. However, when either of them is negative, the formula can produce misleading or incorrect results.

For example, suppose that the budget of a project in 2023 was -$10,000, meaning that the project was expected to lose money. In 2024, the actual result of the project was $12,000, meaning that the project made money. If we use the formula for calculating percent variance, we get:

Percent Variance = (12,000 - (-10,000)) / (-10,000)
Percent Variance = -2.2
Percent Variance = -220%

This result does not make sense, because it implies that the project performed worse than the budget, when in fact it performed better. The problem is that when the old value is negative, the formula reverses the sign of the percent variance, making it negative when it should be positive, and vice versa.

This is a common problem in the corporate world, where budgets and actual results can often be negative values, especially for new or risky ventures.

How to Fix the Problem with Negative Values?

One way to fix the problem with negative values is to use the ABS function in Excel. The ABS function returns the absolute value of a number, which is the number without its sign. For example, ABS(-10) returns 10, and ABS(10) returns 10.

By using the ABS function, we can make sure that the old value in the formula is always positive, regardless of its original sign. This way, the formula will not reverse the sign of the percent variance, and will produce correct and consistent results.

The formula for calculating percent variance with negative values using the ABS function is:

Percent Variance = (New Value - Old Value) / ABS(Old Value)

Using the same example as before, the percent variance with the ABS function would be:

Percent Variance = (12,000 - (-10,000)) / ABS(-10,000)
Percent Variance = 2.2
Percent Variance = 220%

This result makes sense, because it shows that the project performed much better than the budget, by 220%.

How to Apply the Formula in Excel?

To apply the formula for calculating percent variance with negative values in Excel, we can follow these steps:

1. Enter the old values and the new values in two columns, such as column B and column C.
2. Select an empty cell where we want to display the percent variance, such as cell D2.
3. Enter the formula: =(C2-B2)/ABS(B2)
4. Press Enter to get the result.
5. Copy the formula down to the other cells in column D.

The following table shows an example of applying the formula in Excel, using some hypothetical data:

Note that when the old value is zero, the formula returns an error, because we cannot divide by zero. In this case, we can use the IFERROR function to display a different value, such as N/A, blank, or zero. For example, the formula in cell D6 could be:

=IFERROR((C6-B6)/ABS(B6),"N/A")

Are There Other Approaches?

The formula using the ABS function is one of the most common and simple ways to calculate percent variance with negative values. However, it is not the only way, and it may not be the best way for every situation.

Some alternative approaches are:

• Using the average of the old value and the new value as the denominator, instead of the old value. This formula is:

Percent Variance = (New Value - Old Value) / ((Old Value + New Value) / 2)

This formula reduces the impact of large changes in small values, and makes the percent variance symmetric, meaning that the same change in opposite directions will have the same magnitude of percent variance.

• Using the MIN or MAX function to choose the smaller or larger value between the old value and the new value as the denominator, instead of the old value. These formulas are:

Percent Variance = (New Value - Old Value) / MIN(Old Value, New Value)
Percent Variance = (New Value - Old Value) / MAX(Old Value, New Value)

These formulas avoid the problem of dividing by zero, and make the percent variance always positive or always negative, depending on whether we use the MIN or MAX function.

• Using a different measure of change, such as the absolute change or the logarithmic change, instead of the relative change. These formulas are:

Absolute Change = New Value - Old Value
Logarithmic Change = LN(New Value / Old Value)

These formulas do not depend on the sign of the values, and can be useful for comparing changes across different scales or orders of magnitude.

The choice of which approach to use depends on the purpose and context of the analysis, and the preferences and conventions of the audience. There is no one right answer, but it is important to be consistent and transparent about the method used, and to explain the rationale and implications of the chosen formula.