Need help with formula

[Gil]

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.

 

[Don Guillett]

You could use nested ifs but try looking in the help index for CHOOSE or
LOOKUP

 

[Ag]

YOu can structure the formulae asshown below

Input
Salary 1000
no. of days 202

Slabs
min max percentage # of days in slab amount
0 15 0 =IF(B$3>=B7,(B7-A7),IF(B$3>=A7,B$3-A7,0)) =D7*B$2*C7
16 99 0.2 =IF(B$3>=B8,(B8-B7),IF(B$3>=A8,B$3-B7,0)) =D8*B$2*C8
100 149 0.3 =IF(B$3>=B9,(B9-B8),IF(B$3>=A9,B$3-B8,0)) =D9*B$2*C9
150 200 0.4 =IF(B$3>=B10,(B10-B9),IF(B$3>=A10,B$3-B9,0)) =D10*B$2*C10
201 0.5 =IF(B$3>=A11,B$3-B10,0) =D11*B$2*C11

total =SUM(D711) =SUM(E7:E11)
HTH

Regards
Anirudh

 

[joeu2004]

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

Following the examples at http://www.mcgimpsey.com/excel/variablerate.html
(which provides alternatives that you might also want to consider), I
think the following would meet your needs:

=round(B1 * sumproduct(--(A1>{15,99,149,200}), A1-{15,99,149,200},
{0.2,0.1,0.1,0.1}), 2)

where A1 is the number of sick days, and B1 is the value of a sick day.

 

[Gord Dibben]

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

 

[Gil]

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

Hello and thanks for answering.

i tried using 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

i get a figure of about $1,000 which is too low. since i don't
understand the look up function, i can't trouble shoot it. i can see
where the B1 and A1 functions are doing. the formula should return
different amounts for each subset then total it. i set it up
origonally to return different dollar amounts for each condition then
added them up.

does the forumla you wrote take each condition then return a value for
each set of conditions. Example: if i enter in 300 sick days, the
first parameter would be 84 days being paid at 20%, the next 50 days
at 30% and so on.

 

[Gil]

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

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.

 

[Gil]

Following the examples athttp://www.mcgimpsey.com/excel/variablerate.html
(which provides alternatives that you might also want to consider), I
think the following would meet your needs:

=round(B1 * sumproduct(--(A1>{15,99,149,200}), A1-{15,99,149,200},
{0.2,0.1,0.1,0.1}), 2)

where A1 is the number of sick days, and B1 is the value of a sick day.

worked nicely....thank you