Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
I'm using Excel 2003.
Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
One way, I am sure there are others:
First, on your sheet with the 4 statis tales, let's define these ranges with names. For example, say your 500000 for 2-10 agents is in cell A3, and the last premium value of 5000 is in E6 (this would be 5000000 limit with 25000 deductible). Select that range, and then name it (either click in the box to the left of the fx in the formula bar (it should read A3), and type in the name of the table, or you can access it from your menu Insert|Name|Define. If you've selected the range already, that will be in the refers to box, and you just need to type the name and click Add. I have used a specific naming convention for your 2 sample tables (but you would use the same convention for your other two tables). Tbl_Rates_1 and Tbl_Rates_2 and you would obviously just name the 3rd and 4th the same way, Tbl_Rates_3, and Tbl_Rates_4. =IF(OR(A2="",C2="",D2=""),"",VLOOKUP(C2,INDIRECT(" Tbl_Rates_"&LOOKUP(A2,{0,1;11,2;31,3;51,4})),LOOKU P(D2,{2500,2;5000,3;10000,4;25000,5}),FALSE)) This is the formula that would be in B2. Note: I do not know what your 'agent' range for tables 3 and 4 are, so I guessed. Your first table would start at 0 agents, and return a 1 from the Lookup, and if you got to 11 or more agents, it would return a 2, 31 or more agents would return a 3, and I guess at 51 would be 4. This LOOKUP is the last part of your table name, Tbl_Rates_???? and is driven by the number of agents you typed in A2. The 2nd lookup matches the deductible and returns a value 2, 3, 4, or 5, and this will be the column of your table. Then the actual vlookup looks up the limit, uses first lookup to determine table, 2nd lookup to determine column, and is an exact match. your formula in E2 is relatively simple by comparison: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2*B2,"") -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: I'm using Excel 2003. Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
Hi John,
Thanks for your quick reply. I'll have to play with it and I might be back with more questions...... One question I do have, that I'm not sure if you answered or not, is how to reflect the fact that the data is in two separate sheets within the workbook - I don't see reference to that in the IF statement example you've posted. Dee "John C" wrote: One way, I am sure there are others: First, on your sheet with the 4 statis tales, let's define these ranges with names. For example, say your 500000 for 2-10 agents is in cell A3, and the last premium value of 5000 is in E6 (this would be 5000000 limit with 25000 deductible). Select that range, and then name it (either click in the box to the left of the fx in the formula bar (it should read A3), and type in the name of the table, or you can access it from your menu Insert|Name|Define. If you've selected the range already, that will be in the refers to box, and you just need to type the name and click Add. I have used a specific naming convention for your 2 sample tables (but you would use the same convention for your other two tables). Tbl_Rates_1 and Tbl_Rates_2 and you would obviously just name the 3rd and 4th the same way, Tbl_Rates_3, and Tbl_Rates_4. =IF(OR(A2="",C2="",D2=""),"",VLOOKUP(C2,INDIRECT(" Tbl_Rates_"&LOOKUP(A2,{0,1;11,2;31,3;51,4})),LOOKU P(D2,{2500,2;5000,3;10000,4;25000,5}),FALSE)) This is the formula that would be in B2. Note: I do not know what your 'agent' range for tables 3 and 4 are, so I guessed. Your first table would start at 0 agents, and return a 1 from the Lookup, and if you got to 11 or more agents, it would return a 2, 31 or more agents would return a 3, and I guess at 51 would be 4. This LOOKUP is the last part of your table name, Tbl_Rates_???? and is driven by the number of agents you typed in A2. The 2nd lookup matches the deductible and returns a value 2, 3, 4, or 5, and this will be the column of your table. Then the actual vlookup looks up the limit, uses first lookup to determine table, 2nd lookup to determine column, and is an exact match. your formula in E2 is relatively simple by comparison: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2*B2,"") -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: I'm using Excel 2003. Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
In my sample book that I created, my two defined variables, the tble, are as
follows: Tbl_Rates_1 =Rates!$A$2:$E$6 and Tbl_Rates_2 =Rates!$A$9:$E$13 Essentially, the separate sheet name issue is taken care of because it is used in the defined names of the tables. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: Hi John, Thanks for your quick reply. I'll have to play with it and I might be back with more questions...... One question I do have, that I'm not sure if you answered or not, is how to reflect the fact that the data is in two separate sheets within the workbook - I don't see reference to that in the IF statement example you've posted. Dee "John C" wrote: One way, I am sure there are others: First, on your sheet with the 4 statis tales, let's define these ranges with names. For example, say your 500000 for 2-10 agents is in cell A3, and the last premium value of 5000 is in E6 (this would be 5000000 limit with 25000 deductible). Select that range, and then name it (either click in the box to the left of the fx in the formula bar (it should read A3), and type in the name of the table, or you can access it from your menu Insert|Name|Define. If you've selected the range already, that will be in the refers to box, and you just need to type the name and click Add. I have used a specific naming convention for your 2 sample tables (but you would use the same convention for your other two tables). Tbl_Rates_1 and Tbl_Rates_2 and you would obviously just name the 3rd and 4th the same way, Tbl_Rates_3, and Tbl_Rates_4. =IF(OR(A2="",C2="",D2=""),"",VLOOKUP(C2,INDIRECT(" Tbl_Rates_"&LOOKUP(A2,{0,1;11,2;31,3;51,4})),LOOKU P(D2,{2500,2;5000,3;10000,4;25000,5}),FALSE)) This is the formula that would be in B2. Note: I do not know what your 'agent' range for tables 3 and 4 are, so I guessed. Your first table would start at 0 agents, and return a 1 from the Lookup, and if you got to 11 or more agents, it would return a 2, 31 or more agents would return a 3, and I guess at 51 would be 4. This LOOKUP is the last part of your table name, Tbl_Rates_???? and is driven by the number of agents you typed in A2. The 2nd lookup matches the deductible and returns a value 2, 3, 4, or 5, and this will be the column of your table. Then the actual vlookup looks up the limit, uses first lookup to determine table, 2nd lookup to determine column, and is an exact match. your formula in E2 is relatively simple by comparison: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2*B2,"") -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: I'm using Excel 2003. Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
Thanks again John. I'm going to play with it tomorrow so I'll let you know
how it goes. Dee "John C" wrote: In my sample book that I created, my two defined variables, the tble, are as follows: Tbl_Rates_1 =Rates!$A$2:$E$6 and Tbl_Rates_2 =Rates!$A$9:$E$13 Essentially, the separate sheet name issue is taken care of because it is used in the defined names of the tables. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: Hi John, Thanks for your quick reply. I'll have to play with it and I might be back with more questions...... One question I do have, that I'm not sure if you answered or not, is how to reflect the fact that the data is in two separate sheets within the workbook - I don't see reference to that in the IF statement example you've posted. Dee "John C" wrote: One way, I am sure there are others: First, on your sheet with the 4 statis tales, let's define these ranges with names. For example, say your 500000 for 2-10 agents is in cell A3, and the last premium value of 5000 is in E6 (this would be 5000000 limit with 25000 deductible). Select that range, and then name it (either click in the box to the left of the fx in the formula bar (it should read A3), and type in the name of the table, or you can access it from your menu Insert|Name|Define. If you've selected the range already, that will be in the refers to box, and you just need to type the name and click Add. I have used a specific naming convention for your 2 sample tables (but you would use the same convention for your other two tables). Tbl_Rates_1 and Tbl_Rates_2 and you would obviously just name the 3rd and 4th the same way, Tbl_Rates_3, and Tbl_Rates_4. =IF(OR(A2="",C2="",D2=""),"",VLOOKUP(C2,INDIRECT(" Tbl_Rates_"&LOOKUP(A2,{0,1;11,2;31,3;51,4})),LOOKU P(D2,{2500,2;5000,3;10000,4;25000,5}),FALSE)) This is the formula that would be in B2. Note: I do not know what your 'agent' range for tables 3 and 4 are, so I guessed. Your first table would start at 0 agents, and return a 1 from the Lookup, and if you got to 11 or more agents, it would return a 2, 31 or more agents would return a 3, and I guess at 51 would be 4. This LOOKUP is the last part of your table name, Tbl_Rates_???? and is driven by the number of agents you typed in A2. The 2nd lookup matches the deductible and returns a value 2, 3, 4, or 5, and this will be the column of your table. Then the actual vlookup looks up the limit, uses first lookup to determine table, 2nd lookup to determine column, and is an exact match. your formula in E2 is relatively simple by comparison: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2*B2,"") -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dee" wrote: I'm using Excel 2003. Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement - I Think!
Read Help on VLOOKUP then come back with questions
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dee" wrote in message ... I'm using Excel 2003. Here is what I want to do - is it possible? I want to be able to write a formula that will enter the rate in B2 of sheet 1 based on what I've filled in A2, C2 & D2 and then multiply A2 by B2 and put that result in E2. The part I'm struggling with is I have so many variables in sheet 2 and I'm limited to 7 functions in a string. In other words - here is what I want to accomplish: If the # of agents in A2 is between 2 & 10 go to the first group of rates; if the # of agents is between 11 & 30 go to the 2nd group of rates and then based on what C2 and D2 have in them in worksheet 1 - pull the appropriate rate into B2 in worksheet one. There are actually 4 groups of rates - I've only shown 2. Is this possible or would it be easier to simply refer to the rates and enter each number in worksheet #1 manually? I'm really just trying to cut down on data entry time and margin for error. Keep in mind I'm a novice, so if it's possible I'll need the condition in detail. Here are samples from the two worksheets. In worksheet 1 I have the following (we enter the numbers in A2, C2 and D2: A B C D E 1 #of Agents Rate Liimt Deductible Premium 2 4 1000000 5000 In worksheet 2 I have the following (these are static figures that are really just for reference): A B C D E 1 Rates for 2 to 10 Agents 2 Limit 2500 5000 10000 25000 3 500000 2278 2180 2040 2000 4 1000000 2873 2723 2573 2418 5 2000000 3578 3390 3198 3013 6 5000000 5000 5000 5000 5000 7 8 Rates for 11 to 30 Agents 9 Limit 2500 5000 10000 25000 10 500000 2221 2090 1970 1930 11 1000000 2801 2653 2503 2348 12 2000000 3487 3320 3128 2943 13 5000000 4533 4337 4087 3847 Thanks Dee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a little complicated | Excel Worksheet Functions | |||
Complicated If Statement...? | Excel Worksheet Functions | |||
Complicated If statement? | Excel Discussion (Misc queries) | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
semi-complicated nested IF statement | Excel Worksheet Functions |