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
    Wash DC
    --
    hmz
    Wash DC
    hmz, Jun 12, 2009
    #1
    1. Advertising

  2. "hmz" <> wrote in message
    news:...
    > Hi,
    >
    > I am having trouble writing a formula for the following:


    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.


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


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

  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


    "Lawrence Garvin [MVP]" wrote:

    > "hmz" <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I am having trouble writing a formula for the following:

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

    >
    > 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
    >
    >
    hmz, Jun 12, 2009
    #3
  4. hmz

    TBone Guest

    And on the eigth day <>
    did cause the electrons to come together and form the following words:

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


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

    > I think it is an OR statement, but i do not know how to use it.
    > Thanks,


    FYI, it actually doesn't apply here, but the OR statement looks like
    this:

    OR(condition1,condition2)

    -------

    T-Bone
    MCNGP XL
    TBone, Jun 12, 2009
    #4
  5. "TBone" <reply2me@thenewsgroup> wrote in message
    news:Xns9C284E74F3E62replyhere@207.46.248.16...

    > =basepay*12+IF(service<10,0,IF(service<16,(service-10)*basepay,6
    > *basepay))


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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Doug

    Excel 2003 Formula

    Doug, Feb 15, 2004, in forum: Microsoft Certification
    Replies:
    0
    Views:
    536
  2. =?Utf-8?B?R0c=?=

    Formula Question

    =?Utf-8?B?R0c=?=, Mar 15, 2006, in forum: Microsoft Certification
    Replies:
    1
    Views:
    473
    =?Utf-8?B?RHJhZ29uIFJlYm9ybg==?=
    Apr 28, 2006
  3. =?Utf-8?B?QyBBbmRlcnNvbg==?=

    Excel Formula

    =?Utf-8?B?QyBBbmRlcnNvbg==?=, Apr 12, 2006, in forum: Microsoft Certification
    Replies:
    1
    Views:
    553
    Carey Frisch [MVP]
    Apr 12, 2006
  4. Max Sand

    Excel formula for this...

    Max Sand, Sep 2, 2003, in forum: Computer Support
    Replies:
    6
    Views:
    628
  5. sunny

    Simple Excel formula required

    sunny, Jan 6, 2004, in forum: Computer Support
    Replies:
    4
    Views:
    667
Loading...

Share This Page