+ Reply to Thread
Results 1 to 15 of 15

Help calculting negative actuals into positive target

  1. #1
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Help calculting negative actuals into positive target

    I need help with some calculations that don’t seem to add up properly when dividing a negative percent actual into a positive percent target. Unfortunately for this component, results and targets are percentages, not whole numbers. “Normal math” and “negative target formulas” don’t work for this scenario.

    I’ve Google’d, Bing’d, enlisted others and we just cannot seem to figure it out.

    Here’s the issue:
    Scenario 1:
    -15.12% attainment
    14.49% target

    Scenario 2 - Target Lowered from 14.49% to 2.13%:
    -15.12% attainment
    2.13% target

    Scenario 1 although the target is further away from zero, “normal math” (-15.12%/14.49%) provides a smaller negative result of -104.35%.

    Scenario 2 although the target is closer to zero, “normal math” (-15.12%/2.13%) provides a large negative result of -709.86%.

    It doesn’t make sense to me that we are lowering the target from 14.49% to 2.13% but the % attainment gets worse…

    I feel like I need a formula adjustment but I cannot find one on Google or Bing anywhere and my normal Excel contacts struggle with this one as well.

    If you can help - please let me know

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    Hi -

    Percentages compare sizes of two numbers relative to each other, not to 0. Since 15 and 14 are close to the same size, the percentage is close to 1. Since 2 and 15 are "far" apart in size the percentage ratio will be larger. What is it you're trying to do?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Re: Help calculting negative actuals into positive target

    I'm trying to calculate attainment where someone is paid on this result. Prior to the target change from 14.9% to 2.13% their results was "better" (although negative) than after the change even tho the target was lowered. This doesn't seem accurate for their result.

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    OK. It seems like you are wanting to compare ranges between two scenarios, rather than individual numbers. So, with Scenario 1, the RANGE of difference is:

    14.49 - (-15.12) = 29.61

    Scenario 2 is:

    2.13 - (-15.12) = 17.25 (a smaller number than Scenario 1's range, which I think is what you're getting at).

    So, to compare the two RANGES, we could say Scenario 1/Scenario 2 = 29.61/17.25 = 1.72. Meaning Scenario 1 is 1.72 times harder to achieve than Scenario 2.

    The other way you could look at it is to flip it over and calculate 17.25/29.61 = 0.583. Which means Scenario 2 is "worth?" 58.3 % of Scenario 1.

    Does that help?

  5. #5
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Re: Help calculting negative actuals into positive target

    I'm sorry to sound confusing. I really don't want to compare the numbers. It just that when I update the target which is a lower number, the % attainment gets worse - even though the target is easier to obtain. I'm struggling to reconcile how someone can earn less money when they have a lower target...their result did not change, just the target.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    Hm. Can you give me an example that works the way you expect it to work? For example, does it work for positive percentages when you change the target?

  7. #7
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    Something just occurred to me. So the 15.12% is the attainment. It seems like your formula STILL needs the range but would look more like this:

    % complete = attainment/(goal-attainment) So for Scenario 1 it would be -15.12/(14.19-(-15.12)) = -51.06% complete

    Scenario 2, with the lower target is -15.12/(2.13-(-15.12)) = -87.7% complete

    The numbers will be negative simply because your attainment is a negative number. I'm not sure that matters. You could always take the absolute value of the number to make it positive. Anyway, is that close to what you're looking for?

  8. #8
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Re: Help calculting negative actuals into positive target

    It does. If the actuals were positive, it has the results improvement we expect to see. But when they are negative actuals, it is opposite.

    Scenario 1 – negative actuals into positive target
    -15.12% actuals
    14.49% target
    -104.35% attainment

    Scenario 2 – negative actuals into positive target
    Target Lowered from 14.49% to 2.13%:
    -15.12% actual
    2.13% target
    -709.86% attainment


    Scenario 3 – positive actuals into positive target
    +15.12% actuals
    14.49% target
    104.35% attainment

    Scenario 4 – positive actuals into positive target
    Target Lowered from 14.49% to 2.13%
    +15.12% actual
    2.13% target
    709.86% attainment

  9. #9
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    So, the only difference is the negative versus positive numbers. Why don't you just take the absolute value of the numbers so even if they are negative, they will be treated as positive numbers for your calculation? Basically convert it to Scenarios 3 and 4.

  10. #10
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Re: Help calculting negative actuals into positive target

    Unfortunately I cannot do that as it incorrectly pays out the employee. Positive attainment pays more than earned and negative attainment pays less than earned when using that calculation.

  11. #11
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    How do you know it pays out incorrectly? What is the correct answer for -15.12% and 14.49%, and what is the correct answer for -15.12% and 2.13%?

  12. #12
    Registered User
    Join Date
    11-08-2019
    Location
    Louisville, KY
    MS-Off Ver
    10
    Posts
    6

    Re: Help calculting negative actuals into positive target

    Using abs for the numbers would provide a higher than earned attainment.

    Someone who has a -15.12 actual against a 14.49% target should not earn 134.16% attainment or 709.86% against a 2.13% target.

    =ABS(-15.12%)/ABS(14.49%) = 134.16%
    =ABS(-15.12%)/ABS(2.13%) = 709.86%


    Let's look at someone else with a positive actuals:

    =ABS(+7.62)/ABS(14.49%) = 52.59%
    =ABS(+7.62%)/ABS(2.13%) = 357.75%


    The person who has a lower (negative actual) shouldn't get paid higher than the person with a higher (positive actual)

    This is the struggle.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Help calculting negative actuals into positive target

    Maybe taking a fresh look at the scenario would help.
    Assume that the target is an added percentage (column B) to the last value measured (column A)
    From the percentage of target calculate an target value (column C)
    The actual value could then be measured (column D) and a percentage of the target will be calculated (column E)
    The percentage of pay could then calculated based on comparing columns C and D. Although it would seem that a fairer to compare columns A and D.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  14. #14
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Help calculting negative actuals into positive target

    I usally use

    Percentage = 1 + ( ( Actual - Target ) / Abs(Target) )

    That's mean if Actual = Target ( + or - is accepted ) it's got 100%
    if Target < 0 , Actual = 0 then % = 200%
    if Target < 0 , Actual = ABS(Target) then % = 300% [ equal target = 100%, 0 = 200% , Abs(target) = 300% ]

    By the way , it's base on what you want to mesaure, above is for profit (or desire object value).
    If I want to measure expense (or unwanted object value) I may be use a different formula.

    Regards.

  15. #15
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help calculting negative actuals into positive target

    Sorry, I was away for a few days.

    If the current system is to take the actual and divide it by the target, and you want to keep that system, it seems to me negative attainment would either pay $0 or some nominal base pay you grant for making any progress (even negative progress). $50. Or something minimal, whatever that may be in your industry.

    I think part of the problem here is what does negative actual mean? The person lost clients? Lost profit? Lost time? Lost productivity?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 06-01-2018, 03:33 AM
  2. Replies: 2
    Last Post: 09-11-2016, 02:28 AM
  3. Convert negative to positive in sheet containing both positive/negative numbers
    By sa02000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2010, 07:52 PM
  4. Replies: 1
    Last Post: 08-14-2009, 11:17 AM
  5. Replies: 1
    Last Post: 11-20-2008, 01:52 AM
  6. Replies: 2
    Last Post: 11-13-2008, 12:35 AM
  7. Replies: 4
    Last Post: 09-26-2005, 06:05 PM

Tags for this Thread

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