SOLVED

Formatting Negative Numbers

Copper Contributor

I am using the formula =IF(A1>C1, A1-C1, C1-A1). A1-C1 returns a negative number. How can I show the negative number in parentheses. Thank you.

6 Replies

@Brian3302 

You could use custom cell formatting code:

0;(0)

 

Patrick2788_0-1675871189783.png

 

@Patrick2788 

Thank you for your response. I now believe that I did not adequately describe this issue. There are three columns: A1:A300, B1:B300 and C1:C300. I entered the starting number in A1 and entered the ending number in C1. I entered the formula in B1. When a number was entered in A1 and C1, the correct number was returned in B1. However, it did not indicate if the number was positive or negative and I tried to get the negative number in parentheses but was unable to do so.

@Brian3302 

The number that you have calculated is an inherently positive number, equivalent to writing

= ABS(A1 - C1)

It is possible to conditionally format a positive number so that it appears to be negative, but I would not recommend that unless you are trying to falsify the accounts!  That would have its problems too.

If you need to see the sign of the difference, leave the formula as

= A1 - C1

and make any necessary adjustments within dependent formulae by using 

= ABS(B1)  etc.

@Peter Bartholomew 

Thank you for your response. I’m new at this and didn’t understand all of your comments. Here is what I’m trying to do:

 

Column A       Column B     Column C

Start                Change        End

52.                                        67

I enter =IF(A1>C1, A1-C1,C1-A1) and it returns 15 in Column B

 

Column A.       Column B     Column C

   67                                         52

I enter the same formula and it returns 15 in Column B; however this number is negative and needs to be either in parentheses or with a minus sign.

 
Thank you once again for your assistance.

 

best response confirmed by Brian3302 (Copper Contributor)
Solution

@Brian3302 I'd say you just enter =C1-A1 in B1. That will return 15 in the first example and -15 in the second. 

@Riny_van_Eekelen 

 

Thank you for your assistance; the spreadsheet now works as I want it to.

1 best response

Accepted Solutions
best response confirmed by Brian3302 (Copper Contributor)
Solution

@Brian3302 I'd say you just enter =C1-A1 in B1. That will return 15 in the first example and -15 in the second. 

View solution in original post