Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
You can try this:
=IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How do I add that reference. Thanks Again. "CurlerBob" wrote: You can try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
The way the formula is written it will return a result of 0 for under 10
years. If you want text to show try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,"Under 10 Years"))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF( C2=10,0.15,"Under 10 Years"))),0)) You can replace "Under 10 Years" with any text you want. "rpalmer32" wrote: This is almost exactly what I want. Now I need one more condition. The employee has to be employed at least 10 years for any of this to apply. How do I add that reference. Thanks Again. "CurlerBob" wrote: You can try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
Thanks CurlerBob,
Yes the formula does exactly what you say it will. I however left out of my last post that besides the 10 year requirement the employee has to be at the top of the pay grade for 2 years. I have that date and time in another cell. I'll write out in words what I need. If employee X is with the company 10 years or more and has been at the top of their paygrade for 2 or more years then they will receive the increases that were listed. So even though an employee may have been employed for 10 years they may only be half way through their paygrade. You mentioned using a table, if that is easier please advise on that topic. Thank You. "CurlerBob" wrote: The way the formula is written it will return a result of 0 for under 10 years. If you want text to show try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,"Under 10 Years"))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF( C2=10,0.15,"Under 10 Years"))),0)) You can replace "Under 10 Years" with any text you want. "rpalmer32" wrote: This is almost exactly what I want. Now I need one more condition. The employee has to be employed at least 10 years for any of this to apply. How do I add that reference. Thanks Again. "CurlerBob" wrote: You can try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
I see. Try this instead:
=IF(and(A2=1,D2=2),IF(C2=20,0.3,IF(C2=15,0.25,I F(C2=10,0.2,0))),IF(and(A2=2,D2=2),IF(C2=20,0.2 5,IF(C2=15,0.2,IF(C2=10,0.15,0))),0)) where D2 is the number of years at the top of the pay grade. "rpalmer32" wrote: Thanks CurlerBob, Yes the formula does exactly what you say it will. I however left out of my last post that besides the 10 year requirement the employee has to be at the top of the pay grade for 2 years. I have that date and time in another cell. I'll write out in words what I need. If employee X is with the company 10 years or more and has been at the top of their paygrade for 2 or more years then they will receive the increases that were listed. So even though an employee may have been employed for 10 years they may only be half way through their paygrade. You mentioned using a table, if that is easier please advise on that topic. Thank You. "CurlerBob" wrote: The way the formula is written it will return a result of 0 for under 10 years. If you want text to show try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,"Under 10 Years"))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF( C2=10,0.15,"Under 10 Years"))),0)) You can replace "Under 10 Years" with any text you want. "rpalmer32" wrote: This is almost exactly what I want. Now I need one more condition. The employee has to be employed at least 10 years for any of this to apply. How do I add that reference. Thanks Again. "CurlerBob" wrote: You can try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a formula that uses multiple logics.
Perfect. Thanks.
"CurlerBob" wrote: I see. Try this instead: =IF(and(A2=1,D2=2),IF(C2=20,0.3,IF(C2=15,0.25,I F(C2=10,0.2,0))),IF(and(A2=2,D2=2),IF(C2=20,0.2 5,IF(C2=15,0.2,IF(C2=10,0.15,0))),0)) where D2 is the number of years at the top of the pay grade. "rpalmer32" wrote: Thanks CurlerBob, Yes the formula does exactly what you say it will. I however left out of my last post that besides the 10 year requirement the employee has to be at the top of the pay grade for 2 years. I have that date and time in another cell. I'll write out in words what I need. If employee X is with the company 10 years or more and has been at the top of their paygrade for 2 or more years then they will receive the increases that were listed. So even though an employee may have been employed for 10 years they may only be half way through their paygrade. You mentioned using a table, if that is easier please advise on that topic. Thank You. "CurlerBob" wrote: The way the formula is written it will return a result of 0 for under 10 years. If you want text to show try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,"Under 10 Years"))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF( C2=10,0.15,"Under 10 Years"))),0)) You can replace "Under 10 Years" with any text you want. "rpalmer32" wrote: This is almost exactly what I want. Now I need one more condition. The employee has to be employed at least 10 years for any of this to apply. How do I add that reference. Thanks Again. "CurlerBob" wrote: You can try this: =IF(A2=1,IF(C2=20,0.3,IF(C2=15,0.25,IF(C2=10,0. 2,0))),IF(A2=2,IF(C2=20,0.25,IF(C2=15,0.2,IF(C2 =10,0.15,0))),0)) where A2 is the rating and C2 is the years of service. If you have more ratings and year breaks, this way of doing it can become obnoxious. An easier way would be to make a table with the dollar values and do a lookup in the table. "rpalmer32" wrote: I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 rating = .25 I am a novice, so this is a bit over my head. Any help would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CREATING FORMULA IN ONE WORKSHEET BASED ON MULTIPLE CRITERIA IN AN | Excel Worksheet Functions | |||
Creating a formula which would affect multiple cells | Excel Worksheet Functions | |||
Creating multiple charts | Charts and Charting in Excel | |||
Creating a formula to populate information from multiple cells in another workbook | Excel Worksheet Functions | |||
filters logics | Excel Worksheet Functions |