Need If Formula

[Mohammad Younous]

50,000.00, Commission 10%, Minimum Commission 4,000.00

50,001.00 to 2,00,000.00, Commission 6%, Minimum Commission 6,000.00

2,00,001.00 to 5,00,000.00, Commission 5%, Minimum Commission 7,500.00

5,00,001.00 to 10,00,000.00, Commission 2.50%, Minimum Commission 10,000.00

Over 10,00,001.00, Commission 1.50%, Minimum Commission 15,000.00 to Maximum Commission 30,000.00

 

[GraH - Guido]

Hi,

What would be the meaning of the min/max amounts?
For applying the correct commission, if the value is in column A:
=LOOKUP(A2,{50000,50001,200000,500000,1000000},{0.1,0.06,0.05,02025,02015})
or =VLOOKUP(A2,$E$1:$F$5,2,TRUE), where E1:F5 is the range with in E the amounts and in F the commission pct.

 

[Mohammad Younous]

Thanks
But I need commission of that amount

 

[pecoflyer]

Please explain what you want and post a sample sheet

 

[Mohammad Younous]

I Need commission of amount. Commission mention percentage wise, if commission less than minimum commission then have to minimum commission.
Sl. No. Amount Commission
1 50,000.00
2 2,00,000.00
3 5,00,000.00
4 10,00,000.00
5 12,00,000.00

 

[pecoflyer]

Please post a sample sheet with some data and expected results

 

[GraH - Guido]

perhaps
=min(30000,max(LOOKUP(A2,{50000,50001,200000,500000,1000000},{4000,6000,7500,10000,15000}), A2*LOOKUP(A2,{50000,50001,200000,500000,1000000},{0.1,0.06,0.05,02025,02015})))

or =min(30000,max(VLOOKUP(A2,$E$1:$G$5,3,TRUE),A2*VLOOKUP(A2,$E$1:$F$5,2,TRUE)))

 

[Mohammad Younous]

Please post a sample sheet with some data and expected results

 

[AliGW]

You were asked to give your expected results - you have not entered anything in the commission column. You need to do this manually and post again.

Sl. No. Amount Commission
1 45,000.00
2 180,000.00
3 455,000.00
4 890,000.00
5 1,200,000.00
6 25,000.00
7 110,000.00
8 255,000.00
9 565,000.00
10 2,590,000.00
11 1,039,000.00
12 6,556,000.00

 

[Gireesh]

Mohammad Yonous, Try this formula

=IF(AND($B2>=1,$B2<=50000),IF(($B2*10%)<4000,4000,($B2*10%)),IF(AND($B2>=50001,$B2<=200000),IF(($B2*6%)<6000,6000,($B2*6%)),IF(AND($B2>=200001,$B2<=500000),IF(($B2*5%)<7500,7500,($B2*5%)),IF(AND($B2>=500001,$B2<=1000000),IF(($B2*2.5%)<10000,10000,($B2*2.5%)),IF($B2>1000000,IF(($B2*1.5%)<15000,15000,IF(($B2*1.5%)>30000,30000,($B2*1.5%))))))))

 

[Mohammad Younous]

Please post a sample sheet with some data and expected results

Mohammad Yonous, Try this formula

=IF(AND($B2>=1,$B2<=50000),IF(($B2*10%)<4000,4000,($B2*10%)),IF(AND($B2>=50001,$B2<=200000),IF(($B2*6%)<6000,6000,($B2*6%)),IF(AND($B2>=200001,$B2<=500000),IF(($B2*5%)<7500,7500,($B2*5%)),IF(AND($B2>=500001,$B2<=1000000),IF(($B2*2.5%)<10000,10000,($B2*2.5%)),IF($B2>1000000,IF(($B2*1.5%)<15000,15000,IF(($B2*1.5%)>30000,30000,($B2*1.5%))))))))

Thanks a lot

 

[bines53]

Hi mohammad,

If you put the expected commission, from the amount, which is clear enough, I assume you will get a flexible formula.

David

 

[bines53]

Are these parameters correct?

Amount Commission min max
50,000 10.00% 4,000 4,000
2,000,000 6.00% 6,000 6,000
5,000,000 5.00% 7,500 7,500
10,000,000 2.50% 10,000 10,000
100,000,000 1.50% 15,000 30,000


David

 

[GraH - Guido]

I had some typos in my previous reply. Using ";" where you'd normally have ",". Means I always have to "translate" my formulae.

See attached. Both corrected alternatives are in there.