i have an amount of 265000000
and i would like to take into considerattion the following :

if this amount is between 0 and 6000000......then 6000000*2%

if this amount is above 6000000 and also reach to 9000000....then
6000000*2%+9000000*5%

if this amount is above 6000000 and 9000000 and also reach to
15000000...then 6000000*2%+9000000*5%+15000000*7%

if this amount is above 6000000 and 9000000 and 15000000 and also reach to
30000000...then 6000000*2%+9000000*5%+15000000*7%+30000000*11%

if this amount is above 6000000 and 9000000 and 15000000 and 30000000 and
also reach to 60000000....then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15%

if this amount is above 60000000 and 9000000 and 15000000 and 30000000 and
60000000 and also reach to 120000000 OR ABOVE...then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15% +the remaining
number *20%


what should i do in order to make them in a formula in a single cell ????

On Apr 13, 11:14*am, pierre wrote:
i have an amount of *265000000
and i would like to take into considerattion the following :

if this amount is between *0 and 6000000......then 6000000*2%

if this amount is above 6000000 and also reach to 9000000....then
6000000*2%+9000000*5%

if this amount is above 6000000 and 9000000 and also reach to
15000000...then 6000000*2%+9000000*5%+15000000*7%

if this amount is above 6000000 and 9000000 and 15000000 and also reach to
30000000...then 6000000*2%+9000000*5%+15000000*7%+30000000*11%

if this amount is above 6000000 and 9000000 and 15000000 and 30000000 and
also reach to 60000000....then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15%

if this amount is above 60000000 and 9000000 and 15000000 and 30000000 and
60000000 and also reach to 120000000 OR ABOVE...then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15% +the remaining
number *20%

what should i do in order to make them in a formula in a single cell ????

pierre,

you'd have to use nested IF formulas, e.g:

say you have your value of 265000000 in cell D4, then you would have a
formula =IF(D4 < 6000000, 6000000*1.02, IF(D4 < 9000000, ...

you could then repeat this again and again to test if D4 meets a
certain threshold. In my view it would be better to have the
thresholds, eg. 6000000, 12000000, ... and percentages, eg. 1.02,
1.05, ... in cells, and put the cell references in the formula. This
will allow you to spot any mistakes more easily or change theses
values in the future. If you don't want to see them you can always
hide these cells.

So for example if you have the thresholds in cells A1, A2, ... and
percentages in B1, B2, ... then the formula would look like this
(assuming your value is still in D4):

=IF(D4 < A1, A1*B1, IF(D4 < A2, ... and so on

hope this helps

Try this

=IF(A7<6000000,A7*0.02,IF(A7<9000000,120000+(0.05* (A7-6000000)),IF(A7<15000000,270000+(0.07*(A7-9000000)),IF(A7<30000000,690000+(0.11*(A7-15000000)),IF(A7<60000000,2340000+(0.15*(A7-30000000)),684000+(0.2*(A7-60000000)))))))

"pierre" wrote:

i have an amount of 265000000
and i would like to take into considerattion the following :

if this amount is between 0 and 6000000......then 6000000*2%

if this amount is above 6000000 and also reach to 9000000....then
6000000*2%+9000000*5%

if this amount is above 6000000 and 9000000 and also reach to
15000000...then 6000000*2%+9000000*5%+15000000*7%

if this amount is above 6000000 and 9000000 and 15000000 and also reach to
30000000...then 6000000*2%+9000000*5%+15000000*7%+30000000*11%

if this amount is above 6000000 and 9000000 and 15000000 and 30000000 and
also reach to 60000000....then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15%

if this amount is above 60000000 and 9000000 and 15000000 and 30000000 and
60000000 and also reach to 120000000 OR ABOVE...then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15% +the remaining
number *20%


what should i do in order to make them in a formula in a single cell ????

I lost a zero. change one number

from
684000
to
6840000

"pierre" wrote:

i have an amount of 265000000
and i would like to take into considerattion the following :

if this amount is between 0 and 6000000......then 6000000*2%

if this amount is above 6000000 and also reach to 9000000....then
6000000*2%+9000000*5%

if this amount is above 6000000 and 9000000 and also reach to
15000000...then 6000000*2%+9000000*5%+15000000*7%

if this amount is above 6000000 and 9000000 and 15000000 and also reach to
30000000...then 6000000*2%+9000000*5%+15000000*7%+30000000*11%

if this amount is above 6000000 and 9000000 and 15000000 and 30000000 and
also reach to 60000000....then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15%

if this amount is above 60000000 and 9000000 and 15000000 and 30000000 and
60000000 and also reach to 120000000 OR ABOVE...then
6000000*2%+9000000*5%+15000000*7%+30000000*11%+600 00000*15% +the remaining
number *20%


what should i do in order to make them in a formula in a single cell ????

this formula is wrong because you should have the following answers ;

6000000*2/100= 120000
9000000*5/100= 450000
15000000*7/100= 1050000
30000000*11/100= 3300000
60000000*15/100= 9000000
145000000*20/100=29000000

HOW TO DO IT ??

I still think the formula is correct bassed on your requirements. You r
equations below are wrong. When your have 9000000 your get 2% on the first
6000000 then you get 5% on the next 3000000. Here is my table of what gets
added fro each range

Min Max Dollar amount Rate Max Amount Add
0 6000000 0.02 120000
0
6000000 9000000 0.05 150000
120000
9000000 15000000 0.07 420000
270000
15000000 30000000 0.11 1650000
690000
30000000 60000000 0.15 4500000
2340000
60000000 0.2
6840000


"pierre" wrote:

this formula is wrong because you should have the following answers ;

6000000*2/100= 120000
9000000*5/100= 450000
15000000*7/100= 1050000
30000000*11/100= 3300000
60000000*15/100= 9000000
145000000*20/100=29000000

HOW TO DO IT ??