Calculate percentage change between 2 numbers in Excel
Whether it's for business analysis, academic research, or personal finance management, mastering percentage change calculations is essential for effective data analysis. This article will start with the essential formulas for calculating percentage increases and decreases in Excel, and then delve into more advanced operations, including handling negative numbers, dealing with zero values, and reverse-calculating original or new values based on given percentage changes.
- Calculate percentage change with negative numbers
- Calculate percentage change with zero
- Calculate old value based on percentage change
- Calculate new value based on percentage change
Basic Formulas for Calculating Percentage Change
There are two basic formulas to calculate percentage change between two numbers:
Formula 1:
=(new_value - old_value) / old_value
Formula 2:
=new_value / old_value – 1
These formulas can help you determine how much a certain number has increased or decreased in percentage terms. Below you will find two examples of how these formulas can be used to calculate the percentage change between two numbers.
Calculate Percentage Change Between Two Numbers
This section will show you step by step how to calculate percentage increase and decrease between two numbers.
Calculate percentage increase
If the new value is greater than the old value, the result is a percentage increase. As shown in the screenshot below, suppose you have a website where the registered users are growing every year. To calculate the percentage increase of users per year, you can do as follows.
Step 1: Apply the formula to get the result
Select a blank cell (D2 in this case), enter one of the following formulas and press the Enter key to get the result. Select this cell and drag its Fill Handle down to get the rest of the results. And the result is displayed as general numbers as following screenshot shown:
=(C2-B2)/B2=C2/B2-1
Step 2: Format the result as percentage
To format the numbers as percentage, keep the result cells selected, go to select the Percent Style button in the Number group under the Home tab.
Result
The percentage increase of users per year has now been calculated. See screenshot below:
Calculate percentage decrease
If the new value is less than the old value, it's a decrease. The screenshot below shows a drop in product prices. To compute the percentage decrease of these prices, proceed as follows.
Step 1: Apply the formula to get the result
Select a blank cell (D2 in this case), enter one of the following formulas and press the Enter key to get the result. Select this cell and drag its Fill Handle down to get the rest of the results. And the result is displayed as general numbers as following screenshot shown:
=(C2-B2)/B2=C2/B2-1
Step 2: Format the result as percentage
To format the numbers as percentage, keep the result cells selected, go to select the Percent Style button in the Number group under the Home tab.
Result
The percentage decrease of these prices has now been calculated. See screenshot below:
Advanced operations
This section demonstrates different situations you may encounter when calculating percentage change between two numbers.
Calculate percentage change with negative numbers
When calculating percentage changes, you may encounter negative number as follows:
let's illustrate each of these scenarios with specific examples:
Both the two numbers are negative
The standard formula for percentage change is effective even when both values are negative.
For instance, consider a company whose losses increased from $10,000 last quarter (old value is -10,000) to $50,000 this quarter (new value is -50,000). As illustrated below, using the standard formula (=(C2-B2)/B2) yields a 400% increase, indicating the losses have quadrupled from an absolute value of $10,000 to $50,000.
One of the numbers is negative
In this case, you may encounter situations where the old value is negative, or the new value is negative.
Old value is positive, new value is negative
The standard percentage change formula also works fine when the new value is negative.
For example, a stock’s value shifted from positive to negative. It was valued at $10 (old value is 10) last month. This month, due to market downturns, its value dropped to -$10 (new value is -10). To calculate the percentage change between these two numbers, I apply the standard formula (=(C2-B2)/B2) to get the result of -200%. This represents a significant decrease, indicating a shift from a gain to a loss.
Old value is negative, new value is positive
For example, a business moves from loss to profit. Last year, this small business had a loss of $10000 (old value is -10000) and made a profit of $20000 (new value is 20000) this year. If you apply the standard percentage formula to calculate the percentage change, the result will be incorrect.
Here the result -300 is misleading in the actual scenario. It suggests a negative change, implying that things have worsened, but in reality, the situation has improved from a loss to a profit.
In this case, you need to use the ABS function to make the denominator positive ensures that the percentage change result accurately reflects the shift from negative to positive. Here is the new formula:
=(C2-B2)/ABS(B2)
Here the result 300% indicates the actual positive change from loss to profit.
- Be aware that the ABS method may produce misleading results. As illustrated in the screenshot below, all businesses move from loss to profit, with Company F being the most profitable. However, it shows a smaller percentage change compared to others, which makes no sense. Therefore, use this method cautiously!
Calculate percentage change with zero
While calculating percentage changes in excel is very simple, you may run into some difficulties when the calculation involves zeros.
The old value is zero
When the old value is zero, the standard formula returns #DIV/0! Error value. See screenshot:
In this case, the most acceptable solution is to treat the percentage change as 100%. In basic math, changes from 0 to any positive number will be regarded as a 100% increase.
Here are the basic formulas:
Formula 1:
=IFERROR((new value - old value) / old value, 1)
Formula 2:
=IFERROR((new value / old value - 1, 1)
To display the result as 100% rather than #DIV/0! Error, you can apply one of the following formulas:
=IFERROR((C2 - B2) / B2, 1)=IFERROR((C2 / B2) -1, 1)
The new value is zero
When the new value is 0, the result is -1, which is -100%. The result -100% represents a complete decrease. In this case, the result is reasonable because changes from a positive value to 0 will be a 100% decrease. The result is acceptable.
Calculate old value based on percentage change
Sometimes, you may encounter a situation where you know the percentage change and the latest value, now you need to determine the original value. For example, if this year's sales are $12,000, having increased by 20% from last year, how would you find last year's sales? In such cases, you would use the following formula:
The basic formula:
=New value / (1 + Percentage Change)
Select a blank cell such as C2, enter the following formula and press the Enter key to get the result.
=B2/(1+A2)
Calculate new value based on percentage change
The opposite situation is where you know the percentage change and the original value and need to determine the new value. For instance, if this year's sales are $10,000 and you aim for next year's sales to be 20% higher than this year's, how would you calculate next year's sales?
The basic formula:
=Old value * (1 + Percentage Change)
Select a blank cell such as C2, enter the following formula and press the Enter key to get the result.
=B2*(1+A2)
In conclusion, mastering the calculation of percentage change between two numbers in Excel is a versatile skill that can significantly enhance your data analysis capabilities. Whether dealing with straightforward increases and decreases, or more complex scenarios involving negative numbers or zero values, this tutorial provides various formulas for solving these tasks. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
