Results 1 to 10 of 10

Thread: percentage difference between positive and negative numbers

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default percentage difference between positive and negative numbers

    Hello,
    Please refer to the table below

    I have been trying to create a formula to show the percentage increase between the 'without' row and the 'with' row.
    I've tried ((without - with)/with) and also included the ABS command to try and solve the negative number issue but the results are less than accurate (see bottom row)

    Any help would be most gratefully received

    Regards
    Glen

    Read Write Rithmatic
    Total
    with -0.53 0.90 0.16
    without
    -1.58 -0.08 -0.72
    26.27% 66.54% -54.58%

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,530
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Welcome to the Board!

    So, which is your starting value (with or without)?
    What exactly is the "negative number" issue?
    What does a negative represent, and how should it be handled?
    What are your expected results?
    Last edited by Joe4; Feb 15th, 2018 at 01:09 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Quote Originally Posted by Joe4 View Post
    Welcome to the Board!

    So, which is your starting value (with or without)?
    What exactly is the "negative number" issue?
    What does a negative represent, and how should it be handled?
    What are your expected results?
    I'm calculating the reading writing and maths scores for schools who receive music tuition and those that don't
    I averaged the totals of 85 schools using the AVERAGE function (some schools were positive and some negative values) for all 3 types (reading, writing, maths). My aim is to show how better the scores are when music is involved. In all 3 instances the scores are better, BUT, some scores feature negative numbers (as in my table). I might have expected an increase of around 200% (if my maths is right!) in the reading total: from -1.58 (without) to .53 (with)

    Thanks for getting back so quickly
    G

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Forgot to add that the starting value is 'without'

    thanks Joe4

    G

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,530
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    It really depends on what those numbers actually represent.

    For example, you wouldn't say that 4 degrees Fahrenheit is twice as warm as 2 degrees Fahrenheit!
    Just because the number is twice as big doesn't necessarily mean that the actually heat generated is twice as much.
    So we need to understand exactly what these numbers represent and how they are derived for them to have any sort of true meaning.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,133
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    The value being positive or negative has no affect on the logic to calculate % difference

    =(A-B)/A or =(B-A)/A ... % of A difference... you could use either one one would be +, the other - to describe the direction of the difference
    =(B-A)/B or =(A-B)/B ... % of B difference

    which one you choose is what you are trying to describe

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Hi wyndland,

    I hope others on the board will keep me honest.

    I believe the formula should be the change between the old number and the new number, divided by the starting number.

    For example, if i had 10 apples, and gained 3 more apples, the increase would be the change (+3) divided by the starting number (10) for a % change of 30% increase.

    Since you have negative numbers it 'seems' more confusing than it really is. Here's what I came up with:

    A B C D
    1 Read Write Arithmatic
    2 w/out music -1.58 -.08 -.72
    3 w music -.53 .9 .16
    4
    5 % change 66% 1225% 122%


    The formula I wrote for this (assuming it begins in the top left at cell A1):

    =ABS((B3-B2)/B2)


    That said, sometimes showing a % change is not the best way to communicate the impact. In this case, it may be better to show it as a raw increase, "Reading scores show an increase of 105 basis points when music is included..."

    Other veterans please keep me honest.

    gb7



    Quote Originally Posted by wyndland View Post
    Forgot to add that the starting value is 'without'

    thanks Joe4

    G

  8. #8
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,133
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Arithmatic would make a great name for a calculator company in the '50s lol

    you mean arithmetic

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    No no, I mean Eurithmetric! Which would make a great name for a band in the 70s-80s with heavy use of synthesizers.

    I'll get that updated. Nice catch!

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: percentage difference between positive and negative numbers

    Ugh, can't figure out how to edit my post. Sorry, new around here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •