Nested Formula Help

[KevinM]

I am trying to write a formula that will take the total contract value
and times it by the assoicated percentage.

$1-$4,999,999 = 5%
$5,000,000 - $9,999,999 = 10%
$10,000,000+ = 15%

If the contract value is $6,000,000, than it would do ($4,999,999*5%)+
($1,000,001*10%)=$350,000.05

 

[John C]

=(A1<>"")*MIN(A1,4999999)*5%+MAX(0,A1-4999999)*10%+MAX(0,A1-9999999)*15%

 

[Bernard Liengme]

=MIN(A1,4999999)*5%+MAX(0,A1-4999999)*10%+MAX(0,A1-9999999)*15%
And if this is homework, please be sure you can explain how it works <grin>
best wishes

 

[KevinM]

I want to thank you all for the help, but i am not getting the correct
answer back when I try to use you formula. It is double counting
somewhere. I tried the following formula and it will only work for
two out of the three values i try. Is there a limitation for IF
statements?

Value = $15,000,000 - Should return back $1,500,000.15
Value = $ 7,000,000 - Should return back $450,000.05
Value = $3,000,000 - Should return back $150,000

=IF(A1<6000000,(A1*$B$34),IF(A1>6000000&A1<10000000,((A1-6000001)*$B
$35)+(5999999*$B$34),IF(A1>10000000,((A1-10000002)*$B$36)+(3999999*$B
$35)+(5999999*$B$34),"0")))

 

[John C]

=(A1<>"")*MIN(A1,4999999)*5%+MAX(0,MIN(A1,9999999)-4999999)*10%+MAX(0,A1-9999999)*15%

You should still tell us how the formula actually works

 

[KevinM]

again this works for $3mil and $7mil but not $15Mil. When i try it
for $15mil it gives me $800K for an answer and not the correct $1.5Mil
answer

 

[John C]

Perhaps you would be so kind to copy/paste your formula that is giving you
800000?
For me, it gives me the exact answer you were requiring. I modified the
formula to add some additional error checking if A1 is blank
=(A1<>"")*(MIN(A1,4999999)*5%+MAX(0,MIN(A1,9999999)-4999999)*10%)+MAX(0,A1-9999999)*15%
But otherwise, I type 15,000,000 and I get an 1,500,000.10, so I am really
curious what error you made in entering the formula. Did you re-type the
original formula? or did you copy/paste?