Adding a plus or minus sign into a formula that shows the difference between numbers

[Grimlocc]

Hello,

I would like some help with a formula that tells me the difference between numbers in two cells but does so with a + or – sign depending on whether the number is lower than or higher than the cell I am looking at.

For example, I have, in Cell F9, the number 84 and in cell G9, the number 80. The difference is clearly 4 and I have that number in cell H9 but how do I get it to say -4? In cell H9 I would also need it to be flexible so that if the number in cell B changed to, say 88, the number in cell H9 would change to +4?

I have a formula that I found which gives me the difference already: =IF((F9-G9)<=0,G9-F9,F9-G9)

It’s the plus or minus that I don’t know how to do.

Thanks very much for your help!

 

[MrKowz]

I was able to accomplish this using a custom number format using the below example:

Custom Format: +0;-0;0

Excel 2013/2016
	F	G	H
9	84	80	-4
10	84	88	+4
Sheet1

Cell Formulas
Range	Formula
H9	=G9-F9

 

[Scott T]

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]B[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]C[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]84[/COLOR]
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]80[/COLOR]
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]-4.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]84[/COLOR]
[/TD]
[TD="bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]88[/COLOR]
[/TD]
[TD="bgcolor: transparent, align: right"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]+4.00[/COLOR]
[/TD]
[/TR]
</tbody>[/TABLE]


Use formatting Cell C1 and C2
has the custom format, change to match the format you want

+#,##0.00;-#,##0.00

 

[Grimlocc]

Sorry,

I’m pretty new to Excel so you’ll have to forgive my ignorance. Maybe my explanation wasn’t good enough so I’ll try again.

The number in cell F9 (84 at the moment) is likely to stay the same – but MAY change over time. The number in cell G9 will change on a month by month basis. So, I need a formula in cell H9 which is capable of of calculating the difference between the two but with a plus or minus sign in front. I don’t understand how the tow suggestions I have had will do that?

Really do appreciate the help so far guys!

 

[Grimlocc]

Sorry,

I’m pretty new to Excel so you’ll have to forgive my ignorance. Maybe my explanation wasn’t good enough so I’ll try again.

The number in cell F9 (84 at the moment) is likely to stay the same – but MAY change over time. The number in cell G9 will change on a month by month basis. So, I need a formula in cell H9 which is capable of of calculating the difference between the two but with a plus or minus sign in front. I don’t understand how the tow suggestions I have had will do that?

Really do appreciate the help so far guys!

 

[Scott T]

in H9 use

=G9-F9

The custom number format will put a plus sign(+) in front of a positive number and a negative sign(-) in front of a negative number. So when your formula returns a positive 4 you get +4 in the cell and if it returns a negative 4 you get -4. Since the formula returns a negative if G9 is less than F9 you get -4 if G9 is greater than F9 you get +4

 

[MrKowz]

If you need the literal + and - signs to be stored in the cell value, try using the formula

Excel 2013/2016
	F	G	H
9	84	80	-4
10	84	88	+4
Sheet2

Cell Formulas
Range	Formula
H9	=TEXT(G9-F9,"+0;-0;0")

 

[Grimlocc]

Hi, sorry for the delay in replying. I have been away. Thanks for this - I have finally managed to get it to work!