Change against negative numbers

Caly

Board Regular
Joined
Jul 19, 2015
Messages
161
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You are probably confusing the order of operation.
 

Attachments

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

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

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
Back
Top