If you want to know how to find the difference between two numbers (positive or negative) in Excel, then you are in the right place. In this post, we will show how you can find the difference between two numbers or how you can use a formula in Excel to determine the percentage difference between two negative values.
The Definition of Subtraction and Nomenclature
Excel includes a single universal formula that can be used to determine the difference between two integers, dates, and times, among other things. It also has some sophisticated features, such as the ability to apply customized formatting to your findings.
You may compute the number of minutes that have passed between two-time data or the number of weeks that have elapsed between two dates using this function.
Do you want to know which function we are talking about? Then keep reading this post until the end to find out and know how to find the difference between two numbers in Excel.
But first, it would be great if you knew the definition and working principle of the subtraction and nomenclature.
In the above image, you can see the main principle of the subtraction procedures. It has the following arguments:
Minuend: A number or integer from which a second number is to be deducted is known as a minuend. The minuend in the preceding scenario is 20.
Minus Sign (-): The negative sign (-) is used to calculate the differences between the two values.
Subtrahend: The number or value that will be deducted from the minuend is referred to as the subtrahend.
Equal Sign (=): After that, we insert an equal symbol (=).
Difference: When you subtract two numbers, you get a difference.
Now then we understand the working method or subtraction and nomenclature, let’s move on to the main topic of this article.
How To Find The Difference Between Two Numbers (Positive Or Negative) in Excel
In Microsoft Excel, there is no SUBTRACT function that can be used to do the subtraction calculation. So, in order to conduct subtractions, we are going to utilize the ordinary minus (-) sign rather than any formula.
So, now let’s move on to the methods to find the difference between two numbers in Excel:
1. Subtracting Numbers Manually
In this method, we will enter the numbers in the formula and subtract them manually and individually. By saying the formula, we meant the formula bar where we will start writing a formula with an Equal sign; you know what I mean.
So, to find the difference between two numbers, follow the below steps:
- Open the spreadsheet and on the Difference cell, enter the Equal Sign (=) and type the Minuend number, enter the Minus Sign (-), and write the Subtrahend Number.
- Finally, hit the Enter button on your keyboard, and the difference will appear instantly.
In our example, the final formula has become: =80-8
P.S: Keep in mind that when the subtrahend number is negative, just use the parenthesis to insert the value in the subtraction formula, as in the following example: =-60-(-23)
However, in our opinion, using this method is not recommended. There are so many reasons why this method will just kill your precious time. For example:
- When you do have more than single subtraction, you must create a formula for each and every subtraction on your own. Which is very time-consuming.
- It is not possible to use the same formula with another set of integers. So, you cant use the fill handle option.
- It takes a long time since you have to create a formula for each and every set of numbers.
So, what should you do?
Don’t worry, below we have given some of the other methods which are more comfortable to use and very efficient.
2. By Using the Fill Handle Option Instead of Manually Subtracting
Using the fill handle option is one of the most efficient and easy to do tasks in Excel. Because when you have more than one subtraction, you just manually type one formula in the first cell and use the fill handle option to copy and paste the formula to the remaining cells only by double-clicking on it. Isn’t that the most effective and time-saving method you have ever seen?
To employ a formula for more than one set of numbers, we will first create a formula for the first set of numbers and simply drag it down.
As a result, we will use this strategy, which demonstrates why Excel is so beneficial.
So, follow the below steps carefully to do that:
- Open the spreadsheet and on the Difference cell (cell C2), enter the following formula: =A2-B2
- Hit the Enter button on your keyboard, and you will see the difference appears right away.
- Now comes the best part! Select cell C2, and you will see a Plus icon located on the bottom right corner, which is called the Fill Handle Double click on it, and you will see the formula has been copied and pasted to the remaining cells without any sweat.
In this way, you can easily find the difference between two numbers in Excel, whether it is positive or negative.
3. Finding the Absolute Difference Between Two Numbers By Using Formula in Excel
Even though it may be simple for most Excel users to compute the difference between two values or times in a row, how many of you have ever attempted to calculate an absolute difference between the two numbers, as you can see in the picture below?
In this method, we’ll show you how to easily calculate the ABS difference between two numbers in Excel using a simple strategy.
Now, we will calculate the absolute difference between two numbers by using the ABS formula. The ABS () function is mainly used to return the absolute value of a number which is a number without its sign.
Hence, let’s take a look at the syntax of the ABS function.
Syntax of the ABS() function is: ABS(number)
Here in the Number argument, we will subtract the values.
Now, to do that, follow the below steps carefully:
- Open the spreadsheet and on the Difference cell (cell C2), enter the following formula: =ABS(A2-B2)
- Hit the Enter button on your keyboard, and you will see the difference appears right away.
Now, select cell C2 and you will see a Plus icon located on the bottom right corner, which is called the Fill Handle option. Double click on it, and you will see the formula has been copied and pasted to the remaining cells without any sweat.
Once you fill the below-remaining cells, you will see that all the differences have appeared without any signs. That means all the results will become a positive number.
How To Find Percentage Difference Between Two Numbers in Excel
Do you want to know how to find the percentage difference between two numbers in Excel? Then you are in the right place. Here we will now find out the percentage difference between the two numbers in Excel.
Percent Error and Percent Difference are both terms that refer to the same concept. During Percent Difference, we can compare the two experimental numbers to see how they differ.
So, what’s the equation to find the percentage difference? In normal life, we use the below equation:
In this equation, the E1 is the first experimental number, and the E2 is the second experimental number.
Also, you should keep in mind that, in physics, the percentage change ((New value/Old value) -1) and the percentage difference are two separate things.
However, we can easily do the above equation in Excel by using the ABS function. So, to do that, follow the below steps carefully:
- Open the spreadsheet and on the Difference cell (cell C2), enter the following formula: =2*ABS(A2-B2)/(A2+B2)
- Hit the Enter button on your keyboard, and you will see the percentage difference appear right away.
Now, select cell C2 and you will see a Plus icon located on the bottom right corner, which is called the Fill Handle option. Double click on it, and you will see the formula has been copied and pasted to the remaining cells without any sweat.
So, you may now wonder how in the world this function works:
- When the numbers A2 and B2 are compared, the difference (A2-B2) might be negative. As a result, we’ve utilized the ABS() function (ABS(A2-B2)) to get the value absolutely positive (absolute).
- Afterward, we multiplied the absolute number two times and then divided the result by (A2+B2).
How To Find Percentage Difference For Negative Numbers in Excel
When dealing with negative values, it is impossible to determine the percentage difference both conceptually and empirically. In cases where it is not technically feasible to compute them, how else can we do it in Excel?
No, this is not possible to do. Whatever methodology you choose, you will get outcomes that are inaccurate.
In this section, we will demonstrate two ways for calculating the percentage difference of negative integers in Excel, all of which will take you wrongly. That’s why you should be conscious of this before implementing them into your job.
1. Get the %Difference of Negative Numbers By Making It absolute
Consider the following scenario: you would like to find out the percentage of two values, which are:
Old value: -500
New value: 300
As we know: %change = ((New value – Old value )/ Old value) x 100%
When we use this equation to calculate the % change of the two values given above (-500 & 300), the result we will get is:
= ((300 – (-500))/-500)*100%
= 800/(-500)*100%
= -160%
And you know what, this result is a totally wrong answer.
Because you would not get the proper answer if you use this approach, several organizations use the ABS technique. Using this strategy, the fraction is made to be an absolute number.
And it is also incorrect. In the below image, you will notice that -100 to 200 indicates the greatest percent change, whilst -500 to 200 shows the least percent change.
finding-percentage-difference-by-making-it-absolute-final-result
2. Shifting The Numbers To Make These Absolute
This method is also not accurate, although it is quite a difficult procedure. Now let’s see how this method works.
Suppose we have the below two numbers:
Old value: -60
New value: 30
The following two integers will be shifted by utilizing their absolute pluses and then multiplying the result by two: (|-60|+ |30|)*2 = 180
The shifted old value = -60 + 180 = 120
The shifted new value = 20 + 180 = 200
We will now compute their percentage change as follows: ((200-120)/120)*100% = 60%
Now, let’s check whether this technique produces adequate outcomes for us:
absolute-shifted-numbers-result
Examine the final two rows, which show that the percent change from 1 to 100 & 1 to 1000 is about the same (see the table above).
However, things must be: ((100-1)/1)*100% = 99% and ((1000-1)/1)*100% = 999%
No matter whatever procedures you choose, you will not get accurate results.
Now, what would be the answer to this problem?
The solution to the problem:
So, for the time being, we’ll stick with this equation: IF(MIN(old_value, new_value)<=0, “N/A”, (new_value-old_value)/old_value)
And now, after using this correct formula, take a look at the image below that it is the “N/A” value that is returned when either of the two numbers is negative.
Conclusion
Although it may seem complicated, calculating the difference between two positive or negative values in Excel is very simple and straightforward.
However, when you want to determine the percentage change between two negative numbers or between two positive and negative numbers, the task becomes more complicated.
There is no way you can ever calculate the percentage change between two positive and negative values in Excel.
We hope that this article has helped you to know how to find the difference between two numbers (positive or negative) in Excel.
