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
<
[email protected]>
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!
:
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
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
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!
:
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
:
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.
