On Jan 16, 12:49*am, Gord Dibben <gorddibbATshawDOTca wrote:
I think you have a typo at the very end. *Between 100 and 149 should be 30%

For a sliding scale you could use this formula.

=LOOKUP(B1,{0,16,100,150,201},{0,0.2,0.3,0.4,0.5})

With salary in A1 and accrued sick days in B1 *enter

=LOOKUP(B1,{0,16,100,150,201},{0,0.2,0.3,0.4,0.5}) *A1

Gord Dibben *MS Excel MVP

On Tue, 15 Jan 2008 16:00:54 -0800 (PST), Gil wrote:
i am trying to create a calculator of sorts in excel....rather, it is
going to calculate the value in dollars for a sick day buy back at
retirement.

i have no problem writing the formulas to calculate the differnt
percentages, *The idea is one someone is getting ready to retire, they
can enter their salary into one field and the number of days they have
in their sick bank. *the spread sheet would tell them how much they
might be getting back.

the conditions are a sliding scale based on days accrued. *example:
16-99 days @20% * * 100-149 days@30% * * * *150-200@40% * * 201+ @50%. *IF a person
has 155 days on the books, the first 15 are not paid, then the days
between 16-99 are paid at 20% value, the days between 100-149 days are
paid at 40%.

what i did was try to write a set of formulas that look at the number
of days entered into one cell the "155" and then calculate how many
days fall into each catagory then calculate the money for each
catagory. *once done it would total it up.

the problem is the "if" formula will not let me set more then one
formula. *the first cat works fine since there is not an upper and
lower limit but the others i can't figure out how to set up two
parameters. *the formula i had was "=IF(B8=50,SUM(C4-149),0))" *it
does the lower limit fine, but when the number of days exceeds the
cat, it keeps adding them in. *so by the time you get to the end, the
days are too many.

i've never used the solver and i can't seem to figure it out. *any
help would be appreciated.

hello again,

i made a formula error and fixed it....now it looks pretty close, but
my calculations it is still not correct, but very close. it shows
about $657 higher then when i manually calculate it out.

in my example: i had the person with 300 sick days and making
$62,154.18. for the purpose of the calculation, the salary is an
annual salary then divided by 52 weeks and then by 4 days (4-12 hour
days) (*SUM(C3/52)/4) C3 is the annual salary, C4 is the cell for the
number of days. so my formula now looks like this: =LOOKUP(C4,
{0,16,100,150,201},{0,0.2,0.3,0.4,0.5})*C3

when i change the value of the sick days, the calculation does not
change. so if i substitute 350 in C4 nothing happens. if i reverse
C4 and C3, i get a sum of $150.