Using Excel XP. Trying to determine commissions that are on a sliding scale.
Excel will not let me use a calculation as an answer in "value if true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding scale.
Excel will not let me use a calculation as an answer in "value if true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

You absolutely rock, a true asset to this forum.....:thumbup:

"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding scale.
Excel will not let me use a calculation as an answer in "value if true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

On Fri, 10 Jun 2005 12:22:06 -0700, Kentucky Insurance <Kentucky
wrote:

Using Excel XP. Trying to determine commissions that are on a sliding scale.
Excel will not let me use a calculation as an answer in "value if true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

I suspect your IF function is only regurgitating the part of the equation
relevant to the entry in A2. This is because you have enclosed these equations
in quotation marks. Anything between two quotation marks is interpreted by
Excel as being a text string.

You also have brackets "[ ]" in your formula which are not legitimate
characters in a formula. They are sometimes used with cell formatting
instructions, but not in a worksheet function.

Why did you decide to use the quotes and brackets? Is it a carryover from some
other spreadsheet program?

=IF(A2<25000,A2*0.05,IF(A2<75000,(25000*0.05)+((A2-25000)*0.04),(25000*0.05)+(50000*0.04)+((A2-75000)*0.03)))

is your formula without the quotes and brackets. I don't believe it is giving
the correct answers.

I would use a lookup table, though, for this kind of problem.

Set up a Commission Table (I've named it CommissionTable) someplace.

0 $ 0.00 5%
25000 $1,250.00 4%
50000 $2,250.00 3%

Then use this formula:

=VLOOKUP(Premium,CommissionTable,2)+
(Premium-VLOOKUP(Premium,CommissionTable,1))*
VLOOKUP(Premium,CommissionTable,3)

Although this approach is a bit more complex for your problem, it lends itself
more easily to changing commission rates; changing the brackets; changing the
number of brackets, etc.


--ron

BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with the
$/1000 above. But I am not getting the correct amount. I've also tried the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding scale.
Excel will not let me use a calculation as an answer in "value if true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with the
$/1000 above. But I am not getting the correct amount. I've also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding
scale.
Excel will not let me use a calculation as an answer in "value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using
the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with
the
$/1000 above. But I am not getting the correct amount. I've also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding
scale.
Excel will not let me use a calculation as an answer in "value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using
the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we are
awarded the job, we have to pay our insurance company a premium, based on our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to figure out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will calculate
the premium based on the "sliding scale" rate the insurance company has us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount in A2).

Would you give it another try, while I also try to figure it out based on on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am missing it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with
the
$/1000 above. But I am not getting the correct amount. I've also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding
scale.
Excel will not let me use a calculation as an answer in "value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using
the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY
wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we are
awarded the job, we have to pay our insurance company a premium, based on our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to figure out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will calculate
the premium based on the "sliding scale" rate the insurance company has us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount in A2).

Would you give it another try, while I also try to figure it out based on on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am missing it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with
the
$/1000 above. But I am not getting the correct amount. I've also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding
scale.
Excel will not let me use a calculation as an answer in "value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using
the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

Hi, Gord,
Almost there....but not quite. Checked out your answer. Used 2,500,000 in
A2, (assuming proposal was for $2.5M). I know this result would be $9000
premium due (2,500,000/1000)*.3.6.
Your equation gave me $8,000 (incorrect)

I went further and and used a hypothetical Proposal of $2,750,000.
The first 2,500,000 of this = $9000

the next 2,500,000 is rated at $3.20/1000. Which is would be calculated on
the difference between proposal amont of $2,750,000 - 2,500,000 =
250,000/1000 = 250 X 3.2 = $800

Total premium due = $9,800.00
Using your calculation, I get a result of $9,900.00

I've plugged in various other numbers in A2 and again,not getting correct
result. (BTW, in order to accurately calculate premium based on proposal
<$2,500,000, I have to delete the "0" in your formula, which does give me
correct premium, as it picks up the $3.60/$1000 premium rate.)

I'm working on it...see if we can both arrive at the correct formula using
lookup.

Thanks so much.
FMW


MANY THANKS!


"Gord Dibben" wrote:

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY
wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we are
awarded the job, we have to pay our insurance company a premium, based on our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to figure out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will calculate
the premium based on the "sliding scale" rate the insurance company has us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount in A2).

Would you give it another try, while I also try to figure it out based on on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am missing it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first part. I
have to calculate insurance premiums to pay, based on the $ amount of our
estimates to perform work. Insurance rate is based on a sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points with
the
$/1000 above. But I am not getting the correct amount. I've also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a sliding
scale.
Excel will not let me use a calculation as an answer in "value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating using
the
value entered in cell A2, it just regurgitates the whole equation.
Sorry for such a technical question. Thanks for any help.

Biff gave you the link to John McGimpsey's web page where John gives very
detailed instructions on how to solve a problem such as yours.


Try this formula and see if it works for you:

=SUMPRODUCT((A2{0,2500000,5000000,7500000})*(A2-{0,2500000,5000000,7500000})*{0.0036,-0.0004,-0.0004,-0.0008})

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"FMWGARY" wrote in message
...
Hi, Gord,
Almost there....but not quite. Checked out your answer. Used 2,500,000 in
A2, (assuming proposal was for $2.5M). I know this result would be $9000
premium due (2,500,000/1000)*.3.6.
Your equation gave me $8,000 (incorrect)

I went further and and used a hypothetical Proposal of $2,750,000.
The first 2,500,000 of this = $9000

the next 2,500,000 is rated at $3.20/1000. Which is would be calculated on
the difference between proposal amont of $2,750,000 - 2,500,000 =
250,000/1000 = 250 X 3.2 = $800

Total premium due = $9,800.00
Using your calculation, I get a result of $9,900.00

I've plugged in various other numbers in A2 and again,not getting correct
result. (BTW, in order to accurately calculate premium based on proposal
<$2,500,000, I have to delete the "0" in your formula, which does give me
correct premium, as it picks up the $3.60/$1000 premium rate.)

I'm working on it...see if we can both arrive at the correct formula using
lookup.

Thanks so much.
FMW


MANY THANKS!


"Gord Dibben" wrote:

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY

wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we are
awarded the job, we have to pay our insurance company a premium, based on
our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to figure
out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will
calculate
the premium based on the "sliding scale" rate the insurance company has
us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount in
A2).

Would you give it another try, while I also try to figure it out based on
on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am missing
it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first
part. I
have to calculate insurance premiums to pay, based on the $ amount
of our
estimates to perform work. Insurance rate is based on a sliding
scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points
with
the
$/1000 above. But I am not getting the correct amount. I've also
tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a
sliding
scale.
Excel will not let me use a calculation as an answer in "value
if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating
using
the
value entered in cell A2, it just regurgitates the whole
equation.
Sorry for such a technical question. Thanks for any help.

Hi

One way
=MIN(A1,2500000)*3.6%+
MIN(MAX(0,A1-2500000),2500000)*3.2%+
MIN(MAX(0,A1-5000000),2500000)*2.8%+
MIN(MAX(0,A1-7500000))*2%

--
Regards

Roger Govier


"FMWGARY" wrote in message
...
Hi, Gord,
Almost there....but not quite. Checked out your answer. Used
2,500,000 in
A2, (assuming proposal was for $2.5M). I know this result would be
$9000
premium due (2,500,000/1000)*.3.6.
Your equation gave me $8,000 (incorrect)

I went further and and used a hypothetical Proposal of $2,750,000.
The first 2,500,000 of this = $9000

the next 2,500,000 is rated at $3.20/1000. Which is would be
calculated on
the difference between proposal amont of $2,750,000 - 2,500,000 =
250,000/1000 = 250 X 3.2 = $800

Total premium due = $9,800.00
Using your calculation, I get a result of $9,900.00

I've plugged in various other numbers in A2 and again,not getting
correct
result. (BTW, in order to accurately calculate premium based on
proposal
<$2,500,000, I have to delete the "0" in your formula, which does give
me
correct premium, as it picks up the $3.60/$1000 premium rate.)

I'm working on it...see if we can both arrive at the correct formula
using
lookup.

Thanks so much.
FMW


MANY THANKS!


"Gord Dibben" wrote:

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY

wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we
are
awarded the job, we have to pay our insurance company a premium,
based on our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to
figure out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company
of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will
calculate
the premium based on the "sliding scale" rate the insurance company
has us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount
in A2).

Would you give it another try, while I also try to figure it out
based on on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am
missing it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the
first part. I
have to calculate insurance premiums to pay, based on the $
amount of our
estimates to perform work. Insurance rate is based on a
sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage
points with
the
$/1000 above. But I am not getting the correct amount. I've
also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on
a sliding
scale.
Excel will not let me use a calculation as an answer in
"value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of
calculating using
the
value entered in cell A2, it just regurgitates the whole
equation.
Sorry for such a technical question. Thanks for any help.

Thank you very much! Yes, those details worked perfectly...my mistake was
that I was not putting in the "differentials" in the correct order!
Thank you, again.

--
MANY THANKS!


"RagDyeR" wrote:

Biff gave you the link to John McGimpsey's web page where John gives very
detailed instructions on how to solve a problem such as yours.


Try this formula and see if it works for you:

=SUMPRODUCT((A2{0,2500000,5000000,7500000})*(A2-{0,2500000,5000000,7500000})*{0.0036,-0.0004,-0.0004,-0.0008})

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"FMWGARY" wrote in message
...
Hi, Gord,
Almost there....but not quite. Checked out your answer. Used 2,500,000 in
A2, (assuming proposal was for $2.5M). I know this result would be $9000
premium due (2,500,000/1000)*.3.6.
Your equation gave me $8,000 (incorrect)

I went further and and used a hypothetical Proposal of $2,750,000.
The first 2,500,000 of this = $9000

the next 2,500,000 is rated at $3.20/1000. Which is would be calculated on
the difference between proposal amont of $2,750,000 - 2,500,000 =
250,000/1000 = 250 X 3.2 = $800

Total premium due = $9,800.00
Using your calculation, I get a result of $9,900.00

I've plugged in various other numbers in A2 and again,not getting correct
result. (BTW, in order to accurately calculate premium based on proposal
<$2,500,000, I have to delete the "0" in your formula, which does give me
correct premium, as it picks up the $3.60/$1000 premium rate.)

I'm working on it...see if we can both arrive at the correct formula using
lookup.

Thanks so much.
FMW


MANY THANKS!


"Gord Dibben" wrote:

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY

wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we are
awarded the job, we have to pay our insurance company a premium, based on
our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to figure
out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will
calculate
the premium based on the "sliding scale" rate the insurance company has
us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount in
A2).

Would you give it another try, while I also try to figure it out based on
on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am missing
it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the first
part. I
have to calculate insurance premiums to pay, based on the $ amount
of our
estimates to perform work. Insurance rate is based on a sliding
scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage points
with
the
$/1000 above. But I am not getting the correct amount. I've also
tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on a
sliding
scale.
Excel will not let me use a calculation as an answer in "value
if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of calculating
using
the
value entered in cell A2, it just regurgitates the whole
equation.
Sorry for such a technical question. Thanks for any help.

Roger,
Thank you, for the formula. I keeping the MIN way of figuring out my
sliding scales. Appreciate your help.
Hope I can return favor to users.

Thank you,
FMW

"Roger Govier" wrote:

Hi

One way
=MIN(A1,2500000)*3.6%+
MIN(MAX(0,A1-2500000),2500000)*3.2%+
MIN(MAX(0,A1-5000000),2500000)*2.8%+
MIN(MAX(0,A1-7500000))*2%

--
Regards

Roger Govier


"FMWGARY" wrote in message
...
Hi, Gord,
Almost there....but not quite. Checked out your answer. Used
2,500,000 in
A2, (assuming proposal was for $2.5M). I know this result would be
$9000
premium due (2,500,000/1000)*.3.6.
Your equation gave me $8,000 (incorrect)

I went further and and used a hypothetical Proposal of $2,750,000.
The first 2,500,000 of this = $9000

the next 2,500,000 is rated at $3.20/1000. Which is would be
calculated on
the difference between proposal amont of $2,750,000 - 2,500,000 =
250,000/1000 = 250 X 3.2 = $800

Total premium due = $9,800.00
Using your calculation, I get a result of $9,900.00

I've plugged in various other numbers in A2 and again,not getting
correct
result. (BTW, in order to accurately calculate premium based on
proposal
<$2,500,000, I have to delete the "0" in your formula, which does give
me
correct premium, as it picks up the $3.60/$1000 premium rate.)

I'm working on it...see if we can both arrive at the correct formula
using
lookup.

Thanks so much.
FMW


MANY THANKS!


"Gord Dibben" wrote:

Try this one to get premium due.

=LOOKUP($A$2,{0,250000.01,5000000.01,7500000.01},{ 3.6,3.2,2.8,2})*$A$2/1000


Gord Dibben MS Excel MVP

On Sun, 15 Oct 2006 13:58:01 -0700, FMWGARY

wrote:

Hello, Biff,
Tried your formula:
=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})
Did not work.

Perhaps I was not clear in what I was requesting:
In my job, we submit proposals for construction projects and if we
are
awarded the job, we have to pay our insurance company a premium,
based on our
proposal $:
So, say I submit a proposal for exactly $2,500,000, ez enough to
figure out
(2,500,000/1000) = 2500 X $3.60 = a premium due to insurance company
of:
$9,000.00

I have multiple propososals, ranging from $20,000 up to and over
$10,000,000. I am trying to figgure out a formula to use that will
calculate
the premium based on the "sliding scale" rate the insurance company
has us
pay:

Proposal Awarded for:
First 0$-$2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000


So, say for instance my proposal amount in A2 = $441,883.000.
Another proposal amount is $1,200,000.00 (I would insert this amount
in A2).

Would you give it another try, while I also try to figure it out
based on on
the formula you've provided.
(I'm pulling my hair out!!--It's probably so obvious and I am
missing it!!!)

Again, MANY THANKS!


"Biff" wrote:

oops!

Typo:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

Should be:

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.01})

Biff

"Biff" wrote in message
...
Try this:

Based on your table:

Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium

A2 = premium

=SUMPRODUCT(--(A2{0;25000;50000}),(A2-{0;25000;50000}),{0.05;-0.01;-0.05})

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

"FMWGARY" wrote in message
...
BJ,
I've tried your equation...but I can't seem to get past the
first part. I
have to calculate insurance premiums to pay, based on the $
amount of our
estimates to perform work. Insurance rate is based on a
sliding scale:

First 2,500,000 = $3.60 per $1,000
2nd 2,500,000 = $3.20 per $1,000
3rd 2,500,000 = $2.80 per $1,000
over 7,500,000 = $2.00 per $1,000

I used your calculation below, and replaced the percentage
points with
the
$/1000 above. But I am not getting the correct amount. I've
also tried
the
MIN calculation. Both would be helpful.

--
MANY THANKS!


"bj" wrote:

The format of your equation is wrong, try
=IF(A2<25000,A2*.05,IF(A2<75000,25000*.05+(A2-25000)*.04,25000*.05+50000*.04+(A2-75000)*.03))

or simpler
= A2*.03+Min(A2,75000)*.01+min(A2,25000)*.01


"Kentucky Insurance" wrote:

Using Excel XP. Trying to determine commissions that are on
a sliding
scale.
Excel will not let me use a calculation as an answer in
"value if
true" or
"value if false".
Example of commissions:
5% of first $25,000 premium
4% of next $50,000 premium
3% of remaining premium
Here is the IF formula I used:
=IF(A2<25000,"[+A2*.05]",IF(A2<75000,"[(25000*.05)+((A2-25000)*.04)]","[(25000*.05)+(50000*.04)+((A2-75000)*.03)]"))
The IF function itself seems to work, but instead of
calculating using
the
value entered in cell A2, it just regurgitates the whole
equation.
Sorry for such a technical question. Thanks for any help.