If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




calculate the difference between negative and positive values
I need to show the difference between costings for last month, compared
with this month. 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? Sneaky second question!... it would be useful to display, in the above example, "$100 less", rather than "$100". Can this be done? Many thanks 
Ads 
#2




calculate the difference between negative and positive values
When I went to school:
100(200)=100+200=300 [If it was $100 dollars less it would be 0 (zero) this month] so I contend the difference is $300 so the formula is: =A1B1 A1=Last month, B1=This Month For your second question, try: =IF(B1A1<0,ABS(B1A1) & " less",IF(B1A1>0,B1A1 & " more",B1A1)) A1/B1 as above HTH "watty" wrote: > I need to show the difference between costings for last month, compared > with this month. > > 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? > > Sneaky second question!... it would be useful to display, in the above > example, "$100 less", rather than "$100". Can this be done? > > Many thanks > > 
#3




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(A2A1 < 0, text(A1A2,"$0") & " less", if(A2A1 > 0, text(A2A1,"$0") & " more", "no change") Note the reverse order "A1A2" in the "less" case. This produces a positive number like "$100 less" instead of a negative result like "$100 less". 
#4




calculate the difference between negative and positive values
watty wrote: > I need to show the difference between costings for last month, compared > with this month. > > 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? > > Sneaky second question!... it would be useful to display, in the above > example, "$100 less", rather than "$100". Can this be done? > > Many thanks 
#5




calculate the difference between negative and positive values
Thanks for those very useful responses  I have been able to solve the
problem. One question following  using your suggested formulas I am also setting up fields to show that, for example, this month employee A is working for 1 hour 20 minutes longer that last month. This value is expressed as a decimal, i.e. the employee is working 1.33 hours more. The formula however only returns "1 hour more" and does not recognise the fractional part of the number. How can I adjust the formula to display this. Many thanks (and sincere apologies for my terrible maths in the original question!) watty wrote: > I need to show the difference between costings for last month, compared > with this month. > > 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? > > Sneaky second question!... it would be useful to display, in the above > example, "$100 less", rather than "$100". Can this be done? > > Many thanks 
#6




calculate the difference between negative and positive values
watty wrote:
> This value is > expressed as a decimal, i.e. the employee is working 1.33 hours more. > The formula however only returns "1 hour more" and does not recognise > the fractional part of the number. How can I adjust the formula to > display this. =text(A1A2, "0.00") & " more" 
#7




calculate the difference between negative and positive values

Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
how to turn positive to negative values in excel  Paula1  Excel Worksheet Functions  4  February 23rd 06 11:54 PM 
Add sequences of positive then negative numbers  judoist  Excel Discussion (Misc queries)  6  November 26th 05 06:51 AM 
Sumif using dates and positive negative values  Eqa  Excel Discussion (Misc queries)  1  November 9th 05 10:08 AM 
Formula to make Negative Values Positive & Positive Values Negative?  mustard  Excel Discussion (Misc queries)  4  September 26th 05 10:05 PM 
switching values from positive to negative  lob  Excel Worksheet Functions  5  February 19th 05 12:11 AM 