Change against negative numbers

Caly

Board Regular
Joined
Jul 19, 2015
Messages
130
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hi I have data sales last week compared to this week with some being negative but is this the correct representation to show the change compared to last week? The negative numbers are confusing me.

I have year over year data so This Year versus Last Year from both this week (current) and last week (prior).

From that I calculate the change in dollars $ and percents % for this week current and also last week prior.

For one set of data the change is negative. The dollars change ($4,496,394.08) this week and the percent is -12.88%.
Last week the change dollars was ($4,457,938.68) and the percent was -12.94%

Based on the change values from current and prior I’m doing the change in dollars and percents and coming up with the below:
Change($): ($38,455.40)
Change (%): 0.86%
But the change current is higher than prior with it being ($4,496,394.08) and prior was ($4,457,938.68) so why is the margin percent change showing a positive when the dollar change is a negative?

I’m trying to show the difference to say UP or DOWN when comparing the values by writing the formula as below:
=If(the percent change =0,”same”,if(the percent value >0,”UP”,”DOWN”))
The answer in the values above is coming up as UP since the percent change shows positive but is it? Shouldn’t it be DOWN because if I change the formula to
Look at the change age in dollars instead it will say DOWN

Example of the values:
Current week year over year
Last year: $34,910,092.98
This year: $30,413,698.89
Change ($): ($4,496,394.08) formula: =this year minus last year
Change (%): -12.88% formula: =(this year minus last year)/last year

Prior week year over year
Last year: $34,454,142.99
This year: $29,996,204.30
Change ($): ($4,457,938.68)
Change (%): -12.94%

Change based on comparing current to prior week looking at the Change ($) and Change (%):
($): ($38,455.40) formula: =the change $ from current week minus the change $ from prior week which is =($4,496,394.08) - ($4,457,938.68)
(%): 0.86% formula: =($4,496,394.08) - ($4,457,938.68)/($4,457,938.68)
Up or down: answer shows UP because -12.94% from prior week is less than the current week of -12.88% although still a negative But the dollar value is lower so should it be DOWN instead since the prior week change was ($4,457,938.68) and the current week change is ($4,496,394.08)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
199
Office Version
  1. 365
Platform
  1. Windows
You are probably confusing the order of operation.
 

Attachments

  • Change.jpg
    Change.jpg
    38.6 KB · Views: 16
Upvote 0

Forum statistics

Threads
1,195,704
Messages
6,011,206
Members
441,594
Latest member
AVO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top