Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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 
#2




Answer: calculate the difference between negative and positive values
To calculate the difference between negative and positive values in Excel, follow these steps:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




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(B1A10,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 
#4
Posted to microsoft.public.excel.worksheet.functions




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". 
#5
Posted to microsoft.public.excel.worksheet.functions




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 
#6
Posted to microsoft.public.excel.worksheet.functions




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 
#7
Posted to microsoft.public.excel.worksheet.functions




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" 
#8
Posted to microsoft.public.excel.worksheet.functions




calculate the difference between negative and positive values

Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how to turn positive to negative values in excel  Excel Worksheet Functions  
Add sequences of positive then negative numbers  Excel Discussion (Misc queries)  
Sumif using dates and positive negative values  Excel Discussion (Misc queries)  
Formula to make Negative Values Positive & Positive Values Negative?  Excel Discussion (Misc queries)  
switching values from positive to negative  Excel Worksheet Functions 