Complicated If Statement - I Think!

[Dee]

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

 

[John C]

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})),LOOKUP(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,"")

 

[Dee]

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]

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.

 

[Dee]

Thanks again John. I'm going to play with it tomorrow so I'll let you know
how it goes.

Dee

 

[Bernard Liengme]

Read Help on VLOOKUP then come back with questions
best wishes