Gain/Loss Calculation

[hippo88]

Good morning,

I'm trying to write excel formula to take difference between two cells, say J-F and to show that number difference AND the text "gain" or "loss" depends on if the difference is positive (gain) or negative (loss)

if j-f = $2500 cell to show $2500 Gain
or
if j-f = -$110 cell to show -$110 Loss

 

[Joe4]

For values in cells J1 and F1, use this formula:

=TEXT(J1-F1,"$0") & IF(J1>=F1," Gain", " Loss")

 

[hippo88]

For values in cells J1 and F1, use this formula:

=TEXT(J1-F1,"$0") & IF(J1>=F1," Gain", " Loss")

this is great, thank you very much!

 

[Eric W]

You could also use a custom format to leave the value as a number, so it's available for other formulas. For example:

$#" Gain";"-$"#" Loss";" - ";@

Gives this:

Book1
	F				J	K
1	210				100	-$110 Loss
2	500				3000	$2500 Gain
3	33				abc	#VALUE!
4						Text line
5						-
Sheet1

Cell Formulas
Range	Formula
K1:K3,K5	K1	=J1-F1

 

[hippo88]

$#" Gain";"-$"#" Loss";" - ";@

@Eric W where do I add the $#" Gain";"-$"#" Loss";" - ";@ portion?

 

[Joe4]

@Eric W where do I add the $#" Gain";"-$"#" Loss";" - ";@ portion?

Go to the Cell Format menu (can right-click on cell and select "Format Cells" to see it).
Then go to the Number tab
Choose "Custom" under Category
Copy & Paste what Eric gave you in the Type box.

 

[Eric W]

If you have cents in your values, you might want to use

$#.00" Gain";"-$"#.00" Loss";" - ";@

 

[hippo88]

Got it, thank you both.