• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Commission on the basis various criteria

shaikhrulez

shaikhrulez

Active Member
Hello guys, here I'm posting a challenge for you all.

When a certain amount is entered in Cell E3, it would calculate Commission on the basis of below mentioned criteria;

  • If the amount entered is upto Rs. 100,000 the commission charge would be 0.10% or minimum Rs. 150 whichever is higher.
  • If the amount entered is upto Rs. 1,000,000 the commission charge would be 0.05% or minimum Rs. 175 whichever is higher.
  • If the amount entered is over Rs. 1,000,000 the commission charge would be 0.04% or minimum Rs. 600 whichever is higher.
Waiting for the solution from all the excel geeks here ;) Good Luck..!
 

Attachments

  • Sample Workbook.xlsx
    9.5 KB · Views: 5
Last edited:
As I mentioned earlier, this is a challenge, I got the solution but just looking for a better and concise formula if anyone here could come up with. I think the solution I got is bit lengthier.
 
Khalid NGO said:
I had this:

=IF(E3<=100000,MAX(150,E3*0.1%),IF(E3<=1000000,MAX(175,E3*0.05%),MAX(600,E3*0.04%)))
Click to expand...

Bravo, both solutions are giving the desired results. Amazed by the short & quick solution. Looks like everyone is a champ here ;)

Lets, make it a bit more tricky. What if we add another criteria to this;

If in Cell E2 we select customer type Account Holder then commission should be charged as mentioned below
  • If the amount entered is upto Rs. 100,000 the commission charge would be 0.10% or minimum Rs. 150 whichever is higher.
  • If the amount entered is upto Rs. 1,000,000 the commission charge would be 0.05% or minimum Rs. 175 whichever is higher.
  • If the amount entered is over Rs. 1,000,000 the commission charge would be 0.04% or minimum Rs. 600 whichever is higher.
If the customer type Non-Account Holder is selected then commission should be charged as mentioned below.
  • If the amount entered is upto Rs. 100,000 the commission charge would be 0.10% or minimum Rs. 300 whichever is higher.
  • If the amount entered is upto Rs. 1,000,000 the commission charge would be 0.05% or minimum Rs. 400 whichever is higher.
  • If the amount entered is over Rs. 1,000,000 the commission charge would be 0.04% or minimum Rs. 1000 whichever is higher.
And if the customer type Students is selected then commission should be charged as mentioned below.

  • 0.50% or Rs. 25 which ever is less
Sample workbook is attached after including a drop down list in cell E2 for selecting Customer Type.
 

Attachments

  • Sample Workbook2.xlsx
    10.1 KB · Views: 3
shaikhrulez said:
Bravo, both solutions are giving the desired results. Amazed by the short & quick solution. Looks like everyone is a champ here ;)

Lets, make it a bit more tricky. What if we add another criteria to this;

If in Cell E2 we select customer type Account Holder then commission should be charged as mentioned below
  • If the amount entered is upto Rs. 100,000 the commission charge would be 0.10% or minimum Rs. 150 whichever is higher.
  • If the amount entered is upto Rs. 1,000,000 the commission charge would be 0.05% or minimum Rs. 175 whichever is higher.
  • If the amount entered is over Rs. 1,000,000 the commission charge would be 0.04% or minimum Rs. 600 whichever is higher.
If the customer type Non-Account Holder is selected then commission should be charged as mentioned below.
  • If the amount entered is upto Rs. 100,000 the commission charge would be 0.10% or minimum Rs. 300 whichever is higher.
  • If the amount entered is upto Rs. 1,000,000 the commission charge would be 0.05% or minimum Rs. 400 whichever is higher.
  • If the amount entered is over Rs. 1,000,000 the commission charge would be 0.04% or minimum Rs. 1000 whichever is higher.
And if the customer type Students is selected then commission should be charged as mentioned below.

  • 0.50% or Rs. 25 which ever is less
Sample workbook is attached after including a drop down list in cell E2 for selecting Customer Type.
Click to expand...

:)
I think you have not mentioned all criteria of Commission charges for Students...

Here is what I got from your new query:

=IF(E2="Account Holder",IF(E3<=100000,MAX(150,E3*0.1%), IF(E3<=1000000,MAX(175,E3*0.05%),MAX(600,E3*0.04%))), IF(E2="Non-Account Holder",IF(E3<=100000,MAX(300,E3*0.1%), IF(E3<=1000000,MAX(400,E3*0.05%),MAX(1000,E3*0.04%))),MIN(25,E3*0.5%)))

Regards,
 
A little sorter!

=IF(E2="Account Holder",IF(E3<=100000,150,IF(E3>1000000,MAX(600,E3*0.04%),MAX(175,E3*0.05%))),IF(E2="Non-Account Holder",IF(E3<=100000,300,IF(E3>1000000,MAX(1000,E3*0.04%),MAX(400,E3*0.05%))),MIN(25,E3*0.5%)))
 
Thanks @Khalid NGO & @Deepak for your valuable input. I've attached the excel file I'd been using since long. I made this formula some 3 years ago & at that time I wasn't much aware of excel & its cool functions; that's why it is so long & messy, as you guys can see in attached file I made the minimum amount criteria separately :p
To be very honest I didn't even know we can combine so many conditions in a single excel formula, learned a lot from your posts. Thanks :)
 

Attachments

  • Charges Calculation.xlsx
    11.8 KB · Views: 6
shaikhrulez said:
Thanks @Khalid NGO & @Deepak for your valuable input. I've attached the excel file I'd been using since long. I made this formula some 3 years ago & at that time I wasn't much aware of excel & its cool functions; that's why it is so long & messy, as you guys can see in attached file I made the minimum amount criteria separately :p
To be very honest I didn't even know we can combine so many conditions in a single excel formula, learned a lot from your posts. Thanks :)
Click to expand...
Welcome Dear.

You can see some beautiful combination of multiple formulas here:
http://chandoo.org/forum/forums/excel-challenges/

Take care and keep visiting.
 
Actually this was not a challenge! You just in need to correct the path on which you are walking.
 
Deepak said:
Actually this was not a challenge! You just in need to correct the path on which you are walking.
Click to expand...

True to some extent, but while posting this thread I thought I'm on the right track & got the right solution, just because of that I posted it as a challenge.
 
You must log in or register to reply here.
Back
Top