Help Please

Hi,

Wondering if anyone can help, im trying to design a speardsheet for insurance quotes for motor vehicals. i cant figure out a formula to do it though.

I know i need a lot of if statements, so if anyone is anygood at them could they please help me, or if they can figure out an easier way.

Please see attached sheet :

On the sheet the Year and Vehical are not important in the quote what is important is the area, weight, vehical cc,value and cover.

I have put on the right hand side of the sheet all the information and charges .

So to calculate the quote :

I need to select area if Dublin and lough you get chared 6% of the value of your vehical.

If county you get charged 5% of the value of your vehical.

The cover is the type of insurance you want i have put in the charges underneath.

I have done the same for the vehical cc.



Any help would be much appreciated.

Hi

As it stands, try

=J6*INDEX($U$3:$X$6,MATCH(A6,$U$3:$U$6,0),MATCH(L6,$U$3:$X$3,0))

in N6.

Need more detail on the impacts of the other options to be able to provide further help.

rylo

Thanks very much for the help.

Hey yeah thats great the formula you gave me thanks, i also have to add in the weight for commerical vehicals and vehical CC for private vehicals if you look alone the side you will all the weight and CC and the prices charged i have to bring them into the formula somehow.

Ok for commerical vehicals it depends on the weight and cover type for example if the vehical weights 1 ton and has TPFT then 925 is added on to the total.

Really appreciate the help.

Hi

If you can give some more examples of how the other items are included / excluded, or the decision trees on the various combinations, may be able to help more.

Even if you take examples, and hard code the formulas for that scenario....

As it stands, how / when things are used is a bit obscure.


rylo