Nested Formula Help
 

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

Nested Formula Help
 

=(A1<"")*MIN(A1,4999999)*5%+MAX(0,A1-4999999)*10%+MAX(0,A1-9999999)*15%
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"KevinM" wrote:

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

Nested Formula Help
 

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"KevinM" wrote in message
...
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

Nested Formula Help
 

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(A16000000&A1<1000000 0,((A1-6000001)*$B
$35)+(5999999*$B$34),IF(A110000000,((A1-10000002)*$B$36)+(3999999*$B
$35)+(5999999*$B$34),"0")))

Nested Formula Help
 

=(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 :)

--
** John C **

"KevinM" wrote:

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(A16000000&A1<1000000 0,((A1-6000001)*$B
$35)+(5999999*$B$34),IF(A110000000,((A1-10000002)*$B$36)+(3999999*$B
$35)+(5999999*$B$34),"0")))

Nested Formula Help
 

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

Nested Formula Help
 

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?
--
** John C **

"KevinM" wrote:

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