Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vehicle Insurance Premium Calculation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vehicle Insurance Premium Calculation
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 ======= Kutty wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vehicle age | Excel Discussion (Misc queries) | |||
VEhicle Maintenance | Excel Worksheet Functions | |||
vehicle fleet maintenance | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions |