Math Stumper

Posted on 2016-08-23
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!  

Question by:Bright01
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
LVL 27

Expert Comment

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

Expert Comment

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.

Assisted Solution

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)
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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


Accepted Solution

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

Author Closing Comment

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.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Have you ever thought of installing a power system that generates solar electricity to power your house? Some may say yes, while others may tell me no. But have you noticed that people around you are now considering installing such systems in their …
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…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Suggested Courses

630 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