if formula

Discussion in 'Microsoft Certification' started by hmz, Jun 12, 2009.

  1. hmz

    hmz Guest

    Hi,

    I am having trouble writing a formula for the following:

    Severance Plan:

    12 Weeks of base pay, plus an additional 1 week of base pay for every year
    worked service more than 10 years of service, to maximum of 18 weeks in
    total.

    So, in other words, if someone has worked over 10 years, say it is 22 years,
    then they would be entitled to at least a total of 18 weeks (12+6 weeks).

    My problem is writing a formula that captures all three "or" outcomes of (1)
    under 10 years (2) (or) over 10 years but under Max amount of 18 (3) over the
    18 weeks.

    So, I am looking for something to say,

    Salary: 25,000 weekly $480

    Years worked: 20, 5, and 17

    please help! Thanks!
     
    hmz, Jun 12, 2009
    #1
    1. Advertisements

  2. What kind of formula?
    For what program?
    For what programming language?

    You do realize this is not the algebra assistance forum -- it's a forum to
    assist persons seeking Microsoft Certification.

    Nevertheless, I like brain-twister problems like this.

    The challenge with these type of problems is making them much more complex
    than they really are.

    The only thing we need to solve this problem is the number of years worked.

    The key here is not trying to solve the problem blindly, but first
    developing the testing logic and the expected outcomes.

    To TEST the problem we need to TEST with three separate values:
    [a] a value <= 10 years .. which should always produce the minimum 12
    weeks of pay.
    a value >= 16 years .. which should always produce the maximum 18
    weeks of pay
    [c] one of the values 11 thru 15 .. which should produce the values 13
    thru 17 weeks of pay,
    and, in fact, 11 is always 13, 12 is always 14, 13 is always 15, 14 is
    always 16, and 15 is always 17.

    Once you recognize that there are only seven possible answers, and only
    three possible conditions, it's trivial to map the conditions to the
    outcomes, and in this case, we can explicitly calculate the outcome based on
    each of the three conditions. Two conditions have a FIXED outcome (always
    12, always 18); the third condition can actually be derived from simple
    arithmetic. (Severance = Service + 2)

    Thus, the simple formula is this:

    IF Service <= 10 THEN Severance = 12
    ELSE IF Service >= 16 THEN Severance = 18
    ELSE Severance = Service + 2 /// when Severance is 11, 12, 13, 14, or 15


    --
    Lawrence Garvin, M.S., MCITP:EA, MCDBA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)

    MS WSUS Website: http://www.microsoft.com/wsus
    My Websites: http://www.onsitechsolutions.com;
    http://wsusinfo.onsitechsolutions.com
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
     
    Lawrence Garvin [MVP], Jun 12, 2009
    #2
    1. Advertisements

  3. hmz

    hmz Guest

    oh, this is my first time using this site.

    the formula is something like this:

    if(x<10,y,if(x<10<16,16*#,if(x<26,#,#)

    I am trying figure out if x is greater than 10, but also less than another
    number,

    I think it is an OR statement, but i do not know how to use it. Thanks,
    --
    hmz
    Wash DC


     
    hmz, Jun 12, 2009
    #3
  4. hmz

    TBone Guest

    Ya, its off topic, but what the heck, I'll give it a go. It looks like
    Excel, so I'll assume that's what it is.

    You are partly right, but you are putting too much into it.

    You don't need to check x<10 and x<16 (16? you said 18 originally, but
    I'll use your example here) because you already know from the first IF
    that x>=10. Otherwise you wouldn't be in the FALSE side of the first IF
    scenario. Following that, if the value x is not <16, it must be =>16, so
    the maximum amount applies. But you also have to allow for the initial
    12 weeks that everyone gets. So it would go like this:

    =basepay*12+IF(service<10,0,IF(service<16,(service-10)*basepay,6
    *basepay))
    FYI, it actually doesn't apply here, but the OR statement looks like
    this:

    OR(condition1,condition2)
     
    TBone, Jun 12, 2009
    #4
  5. What.. neither of you guys read all of my post! :-(

    IF Service <= 10 THEN Severance = 12
    ELSE IF Service >= 16 THEN Severance = 18
    ELSE Severance = Service + 2 /// when Severance is 11, 12, 13, 14, or 15

    Translated to an Excel formula is this:

    =IF(service<=10,12,IF(service>=16,18,service+2))

    explained....

    =IF(service<=10,12, -- if service less than or equal to ten years,
    severance is the minimum 12 weeks
    IF(service>=16,18, -- if service is greater than or equal to sixteen
    years,
    severance is the maximum 18 weeks
    service+2)) -- else service is 11 thru 15 and severance is that
    value + 2

    That value is given in terms of number of weeks of pay due.

    What the problem does not tell us, is whether basepay is defined in terms of
    weeks, months, or years. That's a relevant point, not specified.

    If basepay is weekly, then the formula is
    =basepay * (IF(service<=10,12,IF(service>=16,18,service+2)))

    If basepay is monthly, then strictly speaking, the formula is:
    =(basepay * 12) / 52) * (IF(service<=10,12,IF(service>=16,18,service+2)))

    And if basepay is annual, then the formula is:
    =(basepay / 52) * (IF(service<=10,12,IF(service>=16,18,service+2)))






    --
    Lawrence Garvin, M.S., MCITP:EA, MCDBA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)

    MS WSUS Website: http://www.microsoft.com/wsus
    My Websites: http://www.onsitechsolutions.com;
    http://wsusinfo.onsitechsolutions.com
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
     
    Lawrence Garvin [MVP], Jun 12, 2009
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.