Another Reason to Hate Insurance Companies (as if you needed one)

[ajricks]

Trying to create similar formulas in two different cells that will
analyze AND calculate these formulas based on a third single-cell
containing a drop-down list.

The single-cell drop-down contains the names of six different insurers
that have been set up on a separate sheet and validated on the data
entry sheet (calling this cell "Quoted Company").

Formula cells need to determine a particular premium surcharge based on
each insurer's premium surcharging method (calling these formula cells
"Premium A" and "Premium B"). Four possible results:

1. Insurer 1 adds a surcharge for one coverage @ a particular rate;

2. Insurer 1 adds a surcharge for a second coverage @ a different
rate;

3. Insurers 2 & 3 add a surcharge for all coverages @ a different
rate;

4. Insurers 4, 5 & 6 do not include a surcharge for any coverages, i.e
formula would simply need to return the text "INCL").


The following formulas work for Insurer 1:

=IF(D15="Name of Insurance Company",ROUND(C39*0.01,0))

* bases calculation on developed base price, i.e. cell C39

=IF(D15="Name of Insurance Company",ROUND(C43*0.02,0))

* bases calculation on developed base price, i.e. cell C43


Need to incorporate Insurers 2-6 into each of the above working
examples but can't find a working formula. Any help is appreciated. :0)

 

[Harlan Grove]

...
....

Formula cells need to determine a particular premium surcharge based
on each insurer's premium surcharging method (calling these formula
cells "Premium A" and "Premium B"). Four possible results:

1. Insurer 1 adds a surcharge for one coverage @ a particular rate;

2. Insurer 1 adds a surcharge for a second coverage @ a different
rate;

3. Insurers 2 & 3 add a surcharge for all coverages @ a different
rate;

4. Insurers 4, 5 & 6 do not include a surcharge for any coverages, i.e
formula would simply need to return the text "INCL").


The following formulas work for Insurer 1:

=IF(D15="Name of Insurance Company",ROUND(C39*0.01,0))

* bases calculation on developed base price, i.e. cell C39

=IF(D15="Name of Insurance Company",ROUND(C43*0.02,0))

* bases calculation on developed base price, i.e. cell C43


Need to incorporate Insurers 2-6 into each of the above working
examples but can't find a working formula. Any help is appreciated.

I'd use a lookup table. Since you have a dropdown list to select insurance
company names, if that list comes from a range, you could just use that
range in your formulas. Put the base prices and surcharges in columns to the
right of it. Then you could use VLOOKUP to pull the surcharges based on the
company selected.

And this is nothing compared to airline pricing.