How can I display a loss or gain (currency)

[brewster56]

If I start with $1000 and lose $250, I would like to somehow indicate that
the remainder ($750 in this case) was actually "down" from the original
$1000. Conversely, I would like to have some indication that a gain was
actually "up" from the original $1000.

 

[~L]

You could use conditional formatting.

If your initial amount is in cell A1, under format, conditional formatting,
choose cell is "less than" and enter $A$1, then choose a format.

 

[Shane Devenshire]

Hi,

If you only want the number for display purposes then

=IF(B1<A1,A1-B1&" - Down",B1-A1&" - Up")

If you want to use the number in future calculations than

=IF(B1<A1,A1-B1,B1-A1)
and then use conditional formatting

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=A1>B1
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=A1>B1
5. Click the Format button and choose a format.
6. Click OK twice

You will need to create a second rule in both cases
=A1<B1

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
=A1>B1

 

[brewster56]

Works perfectly thank you. Mine was more the latter scenario
"=IF(B1<A1,A1-B1,B1-A1) "

 

[brewster56]

Thank you very much this works and in line with other replies.

 

[David Biddulph]

Isn't that just =ABS(A1-B1) ?
--
David Biddulph

Works perfectly thank you. Mine was more the latter scenario
"=IF(B1<A1,A1-B1,B1-A1) "