How to calculate percentage deviation with negative numbers in Excel
The usual formula for calculating percentage deviation works well until you have to deal with negative numbers. If you try changing the value in percentage terms to a negative number, you end up with an ERROR!. In other words, the formula for percentage deviation with a negative number in the numerator, such as =-10/20-1 or like this: =(20--10)/10, will result in an erroneous value of -300%. The question arises, where did the minus three hundred percent come from? To answer this question and to show how to do it correctly, we'll provide a specific example below.
Calculating Percentage Deviation for Negative Numbers
Almost every investment project, until it reaches the break-even point in the first year, incurs more expenses than income. For this, a budget was set in the business plan for the amount of investment portfolio drawdown within -$10,000. Let's say the investment objects were cryptocurrencies like Bitcoin, and in the first very successful year, the return on the investment portfolio soared to +$12,000. Calculating the percentage deviation between the planned investment drawdown and the actual income using the old formula: -$10,000/$12,000-1 gives us -220%!
Why did we get -220% deviation? After all, it is known that the income not only covered the expenses and exceeded the break-even point but also brought a net profit of $12,000! Well, the problem arises when the value of the ratio is negative; mathematical calculation returns the opposite result. In mathematics, a minus times a minus always gives a plus, so the computational operations are disrupted in our formula. Such errors are often made when working with budgets in Excel where planned expenses are expressed in negative numbers.
To solve this problem, you should use the =ABS() function, which converts a negative number into a positive one. That is, the function will find the modulus of the number (its absolute value). The image shows how the modified formula for calculating percentage deviation with negative numbers behaves under any conditions.
As you can see, to ensure that the calculation of percentage deviation in Excel returns the correct result for any positive or negative numbers in the numerator and denominator, you should use the following formula:
=(B15-A15)/ABS(A15)
The image shows that the investment portfolio's returns had to overcome a path of 220% (or $22,000) to go from -$10,000 to +$12,000.
DownloadThis means that the return on the investment portfolio was 120% per annum because: (22000-10000)/10000 = 120%. And if we invest $10,000 at this return rate, in a year, we will not only recover the invested funds but also earn $12,000.
Interesting fact! As of today, the average indicator for risky investments is only 70% per annum.
These formulas can be confidently applied to both negative and positive values. Percentage deviation is returned without errors using the formula with the ABS function.
Description of the ABS Function Example
The ABS function returns the absolute value of a number. If we enter a formula with ABS in cell A1 with arguments containing a negative or positive number, the result of the function will always be a positive number. For example, ABS(-100) = 100. Therefore, the ABS function used in the example described above allows converting a negative number into a positive one before mathematical calculations to find the percentage deviation (in this case, with a negative budget of -$10,000).
As a result, the formula returns an error-free and true value, which can now be used for further calculations and data analysis.
