1

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
2
  • 2
    Then why do you use ABS()?
    – Oleg_S
    Commented Mar 17, 2021 at 11:24
  • 2
    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.
    – AdamV
    Commented Mar 17, 2021 at 12:27

4 Answers 4

3

You said ABS(A1-A2)
A1=10.00

and A2=7.50

To find the difference, just do A1-A2

3
  • 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. Commented Mar 18, 2021 at 8:55
  • 1
    @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
    – Oleg_S
    Commented 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? Commented Mar 18, 2021 at 9:58
0

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                            |
+-------------+--------+---------+
-1

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.

-2

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.