Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Math Stumper

Posted on 2016-08-23
6
Medium Priority
?
78 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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
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.

 

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 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Foreword (May 2015) This web page has appeared at Google.  It's definitely worth considering! https://www.google.com/about/careers/students/guide-to-technical-development.html How to Know You are Making a Difference at EE In August, 2013, one …
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

688 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