# Google Sheets - Formula to find difference between two values and recognize if the number difference is positive or negative

Hope my blabbering will make sense to someone.

I'm trying to create a spreadsheet in Google Sheets to record profits and losses. I've used the `=ABS(A1-A2)` formula to get the difference between two cell values, but I've noticed that it always returns a positive number.

For Example,
`7.5 - 10 = -2.5`   but   `ABS(7.5 - 10) = 2.5`
`10 - 7.5 = 2.5`   and   `ABS(10 - 2.5) = 2.5`

Does anyone know a formula or a way for Google Sheets to always return a difference between 2 numbers as a negative number, regardless of which is larger?

For example where:

``````difference(7.5, 10) = -2.5

# and

difference(10, 7.5) = -2.5
``````
• Then why do you use ABS()? Mar 17, 2021 at 11:24
• To add to @Oleg_S point - ABS is a function which takes the ABSolute value of a number, throwing away the sign. You probably just need =A1-A2 with no functions at all. Mar 17, 2021 at 12:27

You said `ABS(A1-A2)`
A1=10.00

and A2=7.50

To find the difference, just do `A1-A2`

• Yes I am aware of that, that was not my question. My question is: How do I get the the difference between the two numbers to show as "-\$2.50" instead of just "\$2.50". ABS seems to only find the difference, but it does not record whether the difference is positive or negative. Mar 18, 2021 at 8:55
• @KyleNovakovic ABS() returns absolute value, that's why you get \$2.50 instead of -\$2.50. ABS(7.50-10) = ABS(-2.5) = 2.5. You need to simple subtraction A1 - A2 Mar 18, 2021 at 9:23
• @Oleg_S Thanks Oleg, but is there no formula to recognise whether is positive or negative automatically? I'm pretty sure Excel has a formula which does that. Does Google Sheets not having anything similar? Mar 18, 2021 at 9:58

## Difference as a negative

There are many approaches that can be used to always return the difference between two numbers as a negative number. The most obvious is a variation on your current approach with ABS since that wouldn't require any conditional logic. You could also use a conditional IF function which is longer and unnecessary in this case.

ABS Function
As you pointed out, ABS always returns a positive number, therefore you can simply convert the number returned by the ABS function to a negative value. In that manner, you will always return a negative value without ever needing to know anything about the calculation inside. This is both the simplest and shortest approach.

``````=-ABS(num1-num2)
``````
``````# Examples
+-----------------+-----------------+----------------+
| = -ABS(7.5-10)  | = -ABS(10-7.5)  | = -ABS(10-10)  |
| = -ABS(-2.5)    | = -ABS(2.5)     | = -ABS(0)      |
| = -2.5          | = -2.5          | = 0            |
+-----------------+-----------------+----------------+
``````

IF Function
The IF function allows one to conditionally test the numbers and perform subtraction in the preferred order to favor a negative difference.
Syntax: `IF(logical_expression, value_if_true, value_if_false)`

logical_expression:  is `num1` smaller than `num2`
value_if_true:            subtract `num2` from `num1`
value_if_false:           subtract `num1` from `num2`

``````=IF(num1<num2, num1-num2, num2-num1)
``````
``````# Examples
+-------------+--------+---------+
| logicalTest | ifTrue | ifFalse |
+-------------+--------+---------+
| = IF(7.5<10,  7.5-10,  10-7.5) |
| = 7.5-10                       | # True
| = -2.5                         |
+-------------+--------+---------+
| logicalTest | ifTrue | ifFalse |
+-------------+--------+---------+
| = IF(10<7.5,  10-7.5,  7.5-10) |
| = 7.5-10                       | # False
| = -2.5                         |
+-------------+--------+---------+
| logicalTest | ifTrue | ifFalse |
+-------------+--------+---------+
| = IF(10<10,   10-10,   10-10)  |
| = 10-10                        | # False
| = 0                            |
+-------------+--------+---------+
``````

Use an `IF` statement.

Example: `=IF(\$E\$8>E14,\$E\$8-E14,(E14-\$E\$8)*(-1))`

Cell `\$E\$8` is the current stock price.
Cell `E14` is my invested capital

IF the current stock price is higher then my invested capital then `\$E\$8-E14`.
IF the current stock price is lower then my invested capital then `(E14-\$E\$8)*(-1)`.

I had to multiply by (-1) to get the negative to show.

This worked for me: =IFS(A1>A2,MINUS(A2,A1),A1<A2,ABS(MINUS(A1,A2)),A1=A2, "0")

• Welcome to Web Applications. Brevity is acceptable but fuller explanations are better. Mar 8, 2023 at 5:41