Velocity Reviews > if formula

# if formula

hmz
Guest
Posts: n/a

 06-12-2009
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

--
hmz
Wash DC
--
hmz
Wash DC

Lawrence Garvin [MVP]
Guest
Posts: n/a

 06-12-2009
"hmz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
[b] 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 1; 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/pro...awrence.Garvin

hmz
Guest
Posts: n/a

 06-12-2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> [b] 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 1; 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/pro...awrence.Garvin
>
>

TBone
Guest
Posts: n/a

 06-12-2009
And on the eigth day <(E-Mail Removed)>
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

Lawrence Garvin [MVP]
Guest
Posts: n/a

 06-12-2009

> =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/pro...awrence.Garvin