Help with a formula

[leu]

Hello,
I need some help with a formula. It is a nested formula with vlookup
and if.
I am trying to estimate the insurance cost depending on the region, if
the driver is an excellent driver (- $250) or poor driver ( + $ 450)
and if the driver lives in the city ( + $350) or rural ( - $ 400). I
have 5 regions into a different spread sheet, and also the information
about excellent or poor, are into a different spreadsheet.

I got it on the paper but when is to put it in excel I have problems.

Insurance = region ($) + - Excellent, poor driver +- City residence,
rural residence.

Thanks.

 

[Guest]

No ifs or vlookups are needed.


You only have 20 possible cases, so enter them explicitly (cols A-C):

1 poor city
1 poor rural
1 good city
1 good rural
2 poor city
2 poor rural
2 good city
2 good rural
2 poor city
2 poor rural
3 good city
3 good rural
3 poor city
3 poor rural
3 good city
4 good rural
4 poor city
4 poor rural
4 good city
4 good rural

Where 1 is for region#1, etc.

In D1 enter:
=CHOOSE(A1,1000,2000,3000,4000)+450*(B1="poor")-250*(B1="good")+350*(C1="city")-400*(C1="rural")

But update the 1000, 2000, 3000, 4000, 5000 to reflect the real base values
for each region.

Then copy down

 

[Harlan Grove]

No ifs or vlookups are needed.

You only have 20 possible cases, so enter them explicitly (cols A-C):

But don't screw up as in

1 poor city
1 poor rural
1 good city
1 good rural
2 poor city
2 poor rural
2 good city
2 good rural
2 poor city
2 poor rural
3 good city
3 good rural
3 poor city
3 poor rural
3 good city
4 good rural
4 poor city
4 poor rural
4 good city
4 good rural

....

An outstanding example!

There should be 5 regions, not 4. Your table is fubar. There are times
formulas can prevent errors. This seems to be one of those times.

The simple approach is

A1:
=INT((ROW(A1)+3)/4)

B1:
=IF(MOD(ROW(A1)-1,4)<2,"poor","good")

C1:
=IF(MOD(ROW(A1),2)=1,"city","rural")

Select A1:C1 and fill down into A2:C20.

Given the nature of the OP's data, far better to use 3 tables (which are
much easier to expand if necessary than your formulas would be).


Table 1 named BaseRates (modify as needed)

1 1200
2 1300
3 1400
4 1150
5 1375


Table 2 named DrivingRecord

good -250
poor 450


Table 3 named ResidenceType

city 350
rural -400


Then fill out the table with

D1:
=LOOKUP(A1,BaseRates)+LOOKUP(B1,DrivingRecord)+LOOKUP(C1,ResidenceType)

Select D1 and fill down into D220.

 

[leu]

Thank you,

I will give it a try>