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.

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.

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