+ Reply to Thread
Results 1 to 5 of 5

Help with ABS negative percentages and if statements ...

  1. #1
    Registered User
    Join Date
    04-17-2012
    Location
    Park City, UT
    MS-Off Ver
    Excel 2010
    Posts
    3

    Help with ABS negative percentages and if statements ...

    Despite my best efforts I can't make this work. Here is the situation: Analyze the quality of the sales volumes (cell range) worksheet 1. Compare this year's sales volumes with last year's actual annual volumes (cell range) worksheet 2.

    If: Actual exceeded last year by over 20% = Display "Excellent Year". If actual exceeded last year by 10-20% = Display "Good Year". If actual exceeed last year by 0-9.99% = Display "Average Year". If actual did not exceed last year display = "Poor Year".

    So this is the formula I generated with all the IF statements:

    =IF(ABS(F16 -The first cell of data in the column (-) G16 - The corresponding cell on the last year's data sheet)/F16<0.01%,"Poor Year",IF(ABS(F16-G16)/F16>20%,"Excellent Year",IF(ABS(F16-G16)/F16>10%,"Good Year",IF(ABS(F16-G16)/F16>=0.01%,"Average Year")))))

    Most of this works correctly. The percentages are correct, and the "Excellent", "Good", "Average" all display except the "Poor Year". I can't figure out if I have something out of order in the IF statements that is making the formula not display the "Poor" years, or if it has something to do with the negative percentage this value involves. I've looked online everywhere I could and no answer ... so I hope you wonderful people can give a hand. Thanx.
    Last edited by SauceCaptain; 04-17-2012 at 11:37 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,568

    Re: Help with ABS negative percentages and if statements ...

    Think about what the ABS function does -- It converts negative quantities to positive quantities. So effectively the only time it is going to return poor year is when the absolute value is <0.01% or when the actual change is between -0.01% and +0.01%. Effectively, it will only return poor year when F16 is about the same as G16. Anytime F16 is significantly different from G16, it will return one of the other options. I would probably look over the logic in the function and try to eliminate the ABS function so that bad years will return negative values instead of trying to coerce all results to positive values.

  3. #3
    Registered User
    Join Date
    04-17-2012
    Location
    Park City, UT
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Help with ABS negative percentages and if statements ...

    Hmmmm so basically I'm going about this the wrong way? The only option in the short term I could think of is making the "Poor" result by just nesting an IF like this in the initial formula = If actual year is less than previous year = "Poor Year". Any negative percentage by default is going to be a number less than the number from last year, so adding that to the original formula seems to work but it's not really solving the problem of the percentages. But mabey you're right and I need to do something else entirely ... any ideas?
    Last edited by SauceCaptain; 04-17-2012 at 12:06 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,568

    Re: Help with ABS negative percentages and if statements ...

    The point I think I meant to make is that, with the ABS function included, ABS(1-2)/2=+50%. ABS(3-2)/2=+50% as well, so the formula really cannot distinguish between a decrease in sales or an increase in sales.

    On the other hand, drop the ABS and (1-2)/2=-50% vs. (3-2)/2=+50%, and this formula can see the difference between a down year and an up year.

  5. #5
    Registered User
    Join Date
    04-17-2012
    Location
    Park City, UT
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Help with ABS negative percentages and if statements ...

    Thank you! I think this did the trick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1