Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DEE DEE is offline
external usenet poster
 
Posts: 250
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DEE DEE is offline
external usenet poster
 
Posts: 250
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DEE DEE is offline
external usenet poster
 
Posts: 250
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
a little complicated Gaurav[_2_] Excel Worksheet Functions 7 March 18th 08 01:12 AM
Complicated If Statement...? MeiLong Excel Worksheet Functions 4 February 12th 07 10:08 PM
Complicated If statement? aposatsk Excel Discussion (Misc queries) 0 August 3rd 06 06:50 PM
Complicated If Then / V Lookup / Match Statement... ryesworld Excel Worksheet Functions 17 December 10th 05 03:09 PM
semi-complicated nested IF statement tjb Excel Worksheet Functions 3 August 31st 05 09:31 PM


All times are GMT +1. The time now is 01:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"