|
Need help with formula
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. |
Need help with formula
|
Need help with formula
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(D7:D11) =SUM(E7:E11) HTH Regards Anirudh On Jan 16, 5:00*am, 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. |
Need help with formula
On Jan 15, 4:00*pm, Gil wrote:
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. |
Need help with formula
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. |
Need help with formula
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 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. |
Need help with formula
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. |
Need help with formula
On Jan 15, 8:07*pm, joeu2004 wrote:
On Jan 15, 4:00*pm, Gil wrote: 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 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 |
| All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com