Hafiz Islam
You can not find an exact accurate growth formula in Excel with negative numbers. We can use different formulas to calculate growth in Excel with negative numbers, but, there will be misleading or inaccurate results most of the time. In this article, you will learn 3 methods to apply the growth formula in Excel with negative numbers.
Download Practice Workbook
Download the following Excel file for your practice.
3 Examples with Growth Formula in Excel with Negative Numbers
The regular formula for the growth rate between any two numbers is given below.
Growth rate=((New value-Old value)/Old value)*100%.
Suppose, there are 5 different companies, and the incomes of each company in two consecutive years are given. The dataset is given below.
1. Growth Formula in Excel with Positive Old Value and Negative New Value
If the only new values are negative, you can simply apply the same logic and formula that you use if all values are positive. Just follow the steps below.
Steps:
- First, simply type the formula below in cell F5.
=(D5-C5)/C5Where,
C5= OId Value (Income at the previous year)
D5= New Value (Income at this year)
- Press ENTER. We will get the growth rate between these two incomes.
- Drag the Fill Handle to apply the formula to the remaining cells.
Here is the result,
Note:
Since the only new values are negative, that means there are declines from the old value. For this reason, all the growth results are in negative percentages.
Read More: Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
2. Growth Formula in Excel by Making the Denominator Absolute
The regular growth formula will not work for the following two cases,
Case 1: When both the values are negative.
Case 2: When the old values are negative and the new values are positive.
For case 2, as the only old values are negative, the formula will always give the negative values, and that will refer to the negative growth rates or in this illustration, loss for the company while in reality, the produces a profit and so the growth rate should be positive.
For case 1, the same issue will arise except when absolute new values are smaller than absolute old values.
In both of these cases, we need one minor change by making the denominator absolute.
- First, simply type the formula below in cell F5.
=(D5-C5)/ABS(C5)The ABS function will produce the denominator value absolute or positive.
- Press ENTER. We will get the growth rate between these two incomes.
- Drag the Fill Handle to apply the formula to the remaining cells.
Here is the result,
But, there is a complication!
If you look carefully at growth rates for companies B and E. Both the growth rates are positive, and the change in the income of E is equal to B. However, the table shows that B has greater growth than E. In reality, E has gained more profit than B. We will see a roundabout solution to this issue in the next sub-section.
Read More: How to Calculate Year over Year Growth with Formula in Excel (2 Methods)
3. Use IF and MIN Functions in Growth Formula to Avoid Misleading Results Due to Negative Numbers
We will now learn two runarounds that although can not resolve the issue completely, however, will be able to reduce the issue to a much extent.
3.1 No Output for the Negative Numbers
We will search for the negative numbers in both the old and new values. If found, then we will a text to tell the viewer that a growth change is not possible.
Steps:
- Write down the formula below in Cell E5.
=IF(MIN(C5,D5)<=0,"Can Not Be Calculated",(D5/C5)-1)
🔎 Formula Breakdown:
The IF Function will run a logical test (MIN(C5,D5)<=0). If the logical test returns TRUE, the function will give the string “Can Not Be Calculated ”. And if the logical test returns FALSE, then the function will give the growth rate between the two values ((D5/C5)-1).
- Press ENTER. And drag the Fill Handle to the remaining cells.
Here is the result,
3.2. Show Positive or Negative Growth
Another way is to display “Profit” or “Loss” if there is a negative number and the company produces a profit or a loss. Follow the steps below.
Steps:
- Type the following formula in Cell F5.
=IF(MIN(C5,D5)<=0,IF((D5-C5)>0,"Profit","Loss"),(D5/C5)-1)
🔎 Formula Breakdown:
- First IF function will run a logical test
(MIN(C5,D5)<=0)to find if there is a negative number in old and new values. If there is a negative number (TRUE), then it will work on the second IF function. - The second IF test runs another logical test
((D5-C5)>0)to find if the new value is greater than the old value. If the new value is greater than the old value (TRUE), then the second IF function will give the string “Profit” (Which indicates positive growth). And if the new value is smaller than the old value (FALSE), then it will return the string “Loss” (Indicates a negative growth). - If the logical test in the first IF function gives FALSE, then the function will give the growth rate between the two positive values
((D5/C5)-1).
- Press ENTER and drag the Fill Handle to the remaining cells.
Here is the result,
Conclusion
In this tutorial, I have discussed 3 easy methods of calculating growth formulas in excel with negative numbers. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
