Solved

Math Stumper

Posted on 2016-08-23
6
38 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
Comment Utility
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
Comment Utility
-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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Bright01
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CAGR Calculation For SIP 13 60
Graph 5 60
Revenue table 8 70
Math question 3 54
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 …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now