Solved

Math Stumper

Posted on 2016-08-23
6
50 Views
Last Modified: 2016-08-24
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
Comment
Question by:Bright01
6 Comments
 
LVL 27

Expert Comment

by:d-glitch
ID: 41768037
I believe your program is correct.
You will get odd looking results when the before and after change sign.
0
 
LVL 27

Expert Comment

by:d-glitch
ID: 41768042
-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
 
LVL 8

Assisted Solution

by:ShannonEE
ShannonEE earned 250 total points
ID: 41768053
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:Bright01
ID: 41768326
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
 
LVL 4

Accepted Solution

by:
Alexandre Michel earned 250 total points
ID: 41768383
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
 

Author Closing Comment

by:Bright01
ID: 41768626
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question