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.

 calculate the difference between negative and positive values
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## calculate the difference between negative and positive values

#1
October 18th 06, 06:30 AM posted to microsoft.public.excel.worksheet.functions
 watty external usenet poster Posts: 6
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
October 18th 06, 07:38 AM posted to microsoft.public.excel.worksheet.functions
 Toppers external usenet poster Posts: 4,339
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:

=A1-B1 A1=Last month, B1=This Month

=IF(B1-A1<0,ABS(B1-A1) & " less",IF(B1-A1>0,B1-A1 & " more",B1-A1))

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
October 18th 06, 08:01 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 418
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".

#4
October 19th 06, 09:35 AM posted to microsoft.public.excel.worksheet.functions
 watty external usenet poster Posts: 6
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
October 19th 06, 09:42 AM posted to microsoft.public.excel.worksheet.functions
 watty external usenet poster Posts: 6
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
October 19th 06, 03:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 418
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(A1-A2, "0.00") & " more"

#7
October 19th 06, 07:30 PM posted to microsoft.public.excel.worksheet.functions
 watty external usenet poster Posts: 6
calculate the difference between negative and positive values

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 10:54 PM Add sequences of positive then negative numbers judoist Excel Discussion (Misc queries) 6 November 26th 05 05:51 AM Sumif using dates and positive negative values Eqa Excel Discussion (Misc queries) 1 November 9th 05 09: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 18th 05 11:11 PM

All times are GMT +1. The time now is 09:36 AM.