Vehicle Insurance Premium Calculation

[Guest]

I have a list of multiple vehicles to insure. The vehicles are different
types such as Bus, lorry, pick up, car, mini bus. In the list sub headings
as follows :
Type of Vehicle:
Model of Vehilce :
Seating Capacity:
Value of Vehicle :
Rate : Bus 4.5%, lorry 4%, mini bus 3.75%, car 3.5%
in the premium colum I need calculation as per above mentioned criteria. For
example : if the vehicle is bus then multyply by Value of Vehicle X Rate
If the vehicle is lorry then multiply by Value of Vehicle X
Rate
Sometimes some vehicle need only Third party insurance. In this case I dont
want
any calculation except fixed amount as per Type of Vehilce.
For example: Bus $700 , lorry $600, Mini bus $500, car $400

Would appreciate your valuable response.

 

[Dave Peterson]

I would insert a new sheet and build a table with all the possible types of
vehicles.

Column A would contain the type, column B would contain the percentage rate, and
column C would contain the flat rate.

Then I'd apply data|validation to that type of vehicle cell with the data in
column A of that other sheet as my source.

Then I'd add another field to indicate 3rd party or not (y/n) (say B5 holds that
and B1 holds the type of vehicle):

Then I could use a formula like this in B8 (Say):
=if(b1="","",vlookup(b1,sheet2!a:b,if(b5="y",3,2),false))

To retrieve the percentage rate or the flat rate for that vehicle (type is in
B1).

Then I'd use one more cell to get the final answer:

=if(b5="y",b8,b8*b6)
where b6 contains the value of the vehicle.

If you've never used data|validation before, Debra Dalgleish has lots of tips:
http://contextures.com/xlDataVal01.html

And if you've never used =vlookup(), she shares more tips:
http://contextures.com/xlFunctions02.html

=======