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]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.

[Harlan Grove]

"ajricks" wrote...
....
>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.