Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Math Stumper

EE Pros (and Math Wizards),

I have a program that calculates the difference between a "BEFORE" and an "AFTER" number and represents it as a % Change.  It works great when both numbers are positive.  BUT.... if the BEFORE value is negative and the AFTER value is positive, it does not calculate the result correctly.  Either my formula is wrong or I'm dealing with an anomaly in math......... ;-(

See Attached WS/file.

Thank you in advance!  


B.
Measuring-a-Change.xls
0
Bright01
Asked:
Bright01
2 Solutions
 
d-glitchCommented:
I believe your program is correct.
You will get odd looking results when the before and after change sign.
0
 
d-glitchCommented:
-1  to +2  is a change of -300%

The -1 represents 100% of the original value.
A change of +3 is  -3*-1 or 300% of the original value.
0
 
ShannonEECommented:
Hi there Bright01,

What you are dealing with is an anomaly in your thinking.

[You may be confused with relative size - what you are calculating is relative difference]

going from 10 to 12 is a change of a factor of 0.2 or 20%.

going from _10 to _12 is also a change of a factor of 0.2 or 20%.  (grows bigger in the same direction)

going from 10 to 8 is a change of a factor of _0.2 or _20%.            (shrinks or goes back towards zero)

going from 10 to 25 is a change of a factor of 1.5 or 150%.

going from 10 to 5 is a change of a factor of _0.5 or _50%.           (goes back towards zero)

going from 10 to _5 is a change of a factor of _1.5 or _150%.      (goes back towards zero and goes past zero)

Where the base number to the new value is in a different direction (+ or - direction)  to the direction from zero to the base number then you have a negative change.  That if (and only if) going from zero -> base number -> new value involves a change in direction then the relative difference (which is what you are calculating)  will be NEGATIVE.

====

going from _10 to _8 is a change of a factor of _0.2 or _20%.       (stops going negative and changes direction by 20%)

going from _10 to _12 is a change of a factor of 0.2 or 20%.        (grows bigger in the same direction)

going from _10 to 8 is a change of a factor of _1.8 or _180%.       (shrinks the negative number,  goes back towards zero and more)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bright01Author Commented:
Thanks ShannonEE and d-glitch for jumping in here.  

Here's my problem.  The math may be correct, but it flies in the face of logic.  When I have a negative number and turn it positive, logically "it's an improvement".  So the growth, or change should be positive.  But mathematically, it's not.

Say for example, the CURRENT number represents a loss of -3% and the FUTURE is a gain of +2%. That's a difference of +5%.  Is there a way to solve for the difference between the math and the logic in situations where you start with a negative number and move into a positive direction to produce a positive improvement to the change?

Is this logic vs. math?  I'm surprised there is a difference..........

B.
0
 
Alexandre MichelCommented:
Another way to look at it:

If you have a loss of $5 out of $100 (5%), and your loss has increased by 10% (of the $5) to $5.50, then your new loss is 5.5% (of the $100) and your loss has increased by 10% (of the original 5%) . This is why the answer to the original formula is +10%
so when you went for -0.05 to -0.055, you had an increase (of loss) of +10%

What you seem to intuitively want to see is the change of gain which is -10% :-)

If you want to get a positive answer replace the formula in Cell F8
BEFORE Cell F8 =(D8-C8)/C8
AFTER    Cell F8 =(D8-C8)/ABS(C8)
That way if Before number is negative, then your answer will be positive as requested.
If Before is Positive, then the answr stays as is...

Hope it is what you were after
0
 
Bright01Author Commented:
Alexandre,  I used your formula.  You nailed what I was trying to do in addition to answering the question from a math perspective.  ShannonEE, thanks also for your commentary to help me better understand the situation.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now