Are you trying to figure out percentage change or variance? You’re in luck! In this blog post, we’ll show you how to do it step-by-step using Excel. Whether you’re looking for the variance between two numbers or the percentage change from one number to another, we’ve got you covered. Lets’ start at the basics however and clarify what percentage variance or change is.
What Is Percentage Variance Or Change?
The percentage change is used to calculate the proportional percentage change between two values. For example, if you wanted to know the percent change in sales from one month to the next, you would use this function. An original value and a new value are always needed. Examples are this month and last month or original price and sale price. Percentage variance or change is used regularly in business, and in everyday life. You may not notice how much. Percentage change is definitely is on ef my most used calculations in business. You can see a sample below. The variance in sales this year versus last year same month. March is up 13% versus last year whereas April is down by 48% in sales and so on. Very insightful and useful.
The Percentage Change Or Variance Formula
There are a few ways or formulas that will calculate the correct percent change or variance. So, let’s walk through them using the same sales data above.
Formula 1 The Classic
The first formula is what I call the classic. This is the new value, minus the old value, divided by the old value. See below to calculate the percentage change.
(new_value- old_value)/old_value
Working this through our sample data the formula looks simple and is demonstrated below.
Just double click on the first cell to repeat the formula throughout the data set.
Formula 2. The Simple One
The alternative formula is a simpler one, but not used as often in my experience. This formula does return the same result. You still require the old and new value. Divide the new value into the old value then subtract one from the result. The syntax of this simpler formula is below.
(new_value/old_value)-1
Working this through our sample data again I have added the simple formula into the next column to demonstrate that both of the ways of working do give the same result.
Both of these different ways of working do give the result of difference.
Dealing With Errors In Data For Percentage Change
If your data set contains zero values, then you may come across some Excel errors. Unable to divide by zero Excel will display the #DIV/0! error. For example, there are no sales for July 2021 in my data for some reason. Excel cannot divide by zero so will return an error. If you do not need the error displayed then there is a simple solution to dealing with the Excel error. The solution is to wrap the formula in the IFERROR function.
IFERROR Syntax
IFERROR is very simple to implement the syntax is below.
IFERROR(value, value_if_error)
I have re-written the formula to avoid the division by zero error, and chosen to replace any error with a zero. The formula look like this.
=IFERROR((C7-B7)/B7,0) for the classic formula
=IFERROR((C7/B7)-1,0) for the simple formula.
Excel now replaces the error with a zero. This look more ethically pleasing.
In order to calculate the percentage variance or change, you will need to use this simple equation: (new value – old value) / old value. This will give you the percentage variance or change for a given set of numbers. To make things a bit more concrete, let’s take a look at an example. If we wanted to calculate the percentage variance for the number 9 and 12, we would do the following: (12-9) / 9 = 3/9 = .33. This tells us that the number 9 is 33% smaller than 12.
As another example, if we wanted to calculate the percentage change from 100 to 105, we would do the following: (105-100)/100 = 5/100 = .05. This tells us that the number 105 is 5% larger than 100.
Hopefully this article has helped clear up any confusion around calculating percentages! Now that you know how it’s done, put your newfound skills into practice and see how they can help your business grow!
More Excel Articles
