Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
I am wanting to create a statement that will look at the employees number of
years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
Use VLOOKUP()
see: http://www.contextures.com/xlFunctions02.html -- Gary's Student "idribble" wrote: I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
I create a table using your data as follows. For this example, I'm using
Columns O through R. 1-9 10-15 16+ 1 10 15 20 2 12 16 20 3 13 17 20 4 13 17 20 5 15 18 20 6 15 18 20 7 17 19 20 8 17 19 20 9 17 19 20 10 20 20 20 Then I'm using the following formula to determin the number of vacation days. =IF(T1<1,"Less than 1 Year",VLOOKUP(C1,O2:R11,IF(AND(T1=1,T1<10),2,IF(A ND(T19,T1<16),3)))) HTH, Paul "idribble" wrote in message ... I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
=IF(B2=1,VLOOKUP(A2,F1:J7,3,FALSE),IF(B2=2,VLOOKUP (A2,F1:J7,4,FALSE),"20
days")) Column A is "Service Rank" ("At least ....") 1 through 6 Column B is Grade 1 through 3 Column F and G list Ranks and descriptions Column H lists comparative days in Grade 1 Column I the same in Grade 2 Column J the same in Grade 3 -- Best wishes, Jim "PCLIVE" wrote: I create a table using your data as follows. For this example, I'm using Columns O through R. 1-9 10-15 16+ 1 10 15 20 2 12 16 20 3 13 17 20 4 13 17 20 5 15 18 20 6 15 18 20 7 17 19 20 8 17 19 20 9 17 19 20 10 20 20 20 Then I'm using the following formula to determin the number of vacation days. =IF(T1<1,"Less than 1 Year",VLOOKUP(C1,O2:R11,IF(AND(T1=1,T1<10),2,IF(A ND(T19,T1<16),3)))) HTH, Paul "idribble" wrote in message ... I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
Thanks. Didn't have any luck with that feature. Even downloaded a sample
copy. It needs to look at the Grade # and then the # of years, and then pull that number from that box, but limited options on the VLookUp. It needs to look at a separate cell with the # of years worked, then another cell that has the Grade level, and then determine how much vacation. "Gary''s Student" wrote: Use VLOOKUP() see: http://www.contextures.com/xlFunctions02.html -- Gary's Student "idribble" wrote: I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
I just read my post and realized I was not very clear.
Here is the Column/Row setup I attempted to describe. A B C D E F G 1 Rank Grade Vac Days 1 At least 1 but < 2 years 2 1 2 15 Days 2 At least 2 but < 3 years 3 3 etc 4 4 5 5 6 6 7 H I J 10 days 15 days 20 days 12 days 16 days 20 days 13 days 17 days 20 days 15 days 18 days 20 days 17 days 19 days 20 days 20 days 20 days 20 days -- Best wishes, Jim "Jim Jackson" wrote: =IF(B2=1,VLOOKUP(A2,F1:J7,3,FALSE),IF(B2=2,VLOOKUP (A2,F1:J7,4,FALSE),"20 days")) Column A is "Service Rank" ("At least ....") 1 through 6 Column B is Grade 1 through 3 Column F and G list Ranks and descriptions Column H lists comparative days in Grade 1 Column I the same in Grade 2 Column J the same in Grade 3 -- Best wishes, Jim "PCLIVE" wrote: I create a table using your data as follows. For this example, I'm using Columns O through R. 1-9 10-15 16+ 1 10 15 20 2 12 16 20 3 13 17 20 4 13 17 20 5 15 18 20 6 15 18 20 7 17 19 20 8 17 19 20 9 17 19 20 10 20 20 20 Then I'm using the following formula to determin the number of vacation days. =IF(T1<1,"Less than 1 Year",VLOOKUP(C1,O2:R11,IF(AND(T1=1,T1<10),2,IF(A ND(T19,T1<16),3)))) HTH, Paul "idribble" wrote in message ... I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
See my response. You can use VLOOKUP to find the number of years based on
the table I created. Then you need to return the the number of days based on the Grade level. In the VLOOKUP function, you have to tell it which column of your table you want the result pulled from. To get the appropriate column to correspond with the Grade level, the formula looks and the grade level and assigns a number (a column number). Try it. First, create your table. As in the example I gave: From O2 through O11, enter the numbers 1-10. These numbers represent the number of years. From P2 through P11, enter the number of vacation days that correspond to the number of years of service for grades 1-9. From Q2 through Q11, enter the number of vacation days that correspond to the number of years of service for grades 10-15. From R2 through R11, enter the number of vacation days that correspond to the number of years of service for grades 16+. The table will basically look like this. 1 10 15 20 2 12 16 20 3 13 17 20 4 13 17 20 5 15 18 20 6 15 18 20 7 17 19 20 8 17 19 20 9 17 19 20 10 20 20 20 Then your VLOOKUP formula will look like this: =IF(T1<1,"Less than 1 Year",VLOOKUP(C1,O2:R11,IF(AND(T1=1,T1<10),2,IF(A ND(T19,T1<16),3)))) HTH, Paul "idribble" wrote in message ... Thanks. Didn't have any luck with that feature. Even downloaded a sample copy. It needs to look at the Grade # and then the # of years, and then pull that number from that box, but limited options on the VLookUp. It needs to look at a separate cell with the # of years worked, then another cell that has the Grade level, and then determine how much vacation. "Gary''s Student" wrote: Use VLOOKUP() see: http://www.contextures.com/xlFunctions02.html -- Gary's Student "idribble" wrote: I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with IF Statement
Thank you. Finally got to try it and it worked. Appreciate all the posts
and everyone that helps out not just me but all others too. Kudos! "PCLIVE" wrote: I create a table using your data as follows. For this example, I'm using Columns O through R. 1-9 10-15 16+ 1 10 15 20 2 12 16 20 3 13 17 20 4 13 17 20 5 15 18 20 6 15 18 20 7 17 19 20 8 17 19 20 9 17 19 20 10 20 20 20 Then I'm using the following formula to determin the number of vacation days. =IF(T1<1,"Less than 1 Year",VLOOKUP(C1,O2:R11,IF(AND(T1=1,T1<10),2,IF(A ND(T19,T1<16),3)))) HTH, Paul "idribble" wrote in message ... I am wanting to create a statement that will look at the employees number of years that they are working, and then look at their grade level, and then determine the number of vacation days they are warranted. I have tried the basic =IF(AND(C16<=9, T16=1), "10", "0")+IF(AND.... but that statet gets too long with more than 47 +IF statements which are not allowed. Need if I have included an example chart. Length of Service Grades 1-9 Grades 10-15 Grades 16+ At least 1 but < 2 years 10 days 15 days 20 days At least 2 but < 3 years 12 days 16 days 20 days At least 3 but < 5 years 13 days 17 days 20 days At least 5 but < 7 years 15 days 18 days 20 days At least 7 but < 10 years 17 days 19 days 20 days 10 years or more 20 days 20 days 20 days Basically if they are a grade 11 with 7 years of service, I want it to say 19. For a Grade 14 with 5 years of service, to say 18 days. I can do the less than 9 years, but can't figure out to check grades 10-15, and 3-4 years. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions |