View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default calculate the difference between negative and positive values

watty wrote:
For example, last month a particular costing showed a profit of $100.
This month is showing a loss of -$200. I need to show that this month
the difference between the costings is therefore -$100, or, $100
dollars less than last month. Can anyone offer me the formula to do this?


Ah, the difference is -$300, not -$100. The signed difference is
"newValue - oldValue". By "signed difference", I mean: negative for
less; positive for more. In this case, (-200) - 100 = -300. If
"oldValue" is in A1 and "newValue" is in A2, the difference (A3) is:

=A2 - A1

Sneaky second question!... it would be useful to display, in the above
example, "$100 less", rather than "-$100". Can this be done?


In A3:

=if(A2-A1 < 0, text(A1-A2,"$0") & " less", if(A2-A1 0,
text(A2-A1,"$0") & " more", "no change")

Note the reverse order "A1-A2" in the "less" case. This produces a
positive number like "$100 less" instead of a negative result like
"-$100 less".