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.