# Problem with calculate commission

Hello All,

I have many conditions to calculate the following commission From 50,001 to 100,000 = 10% From 100,001 to = 20%
Example:
1) Net of commission 55,000 = 5000*10%=500

2) Net of commission 105,000 = 50,000*10%= 5000
5,000*20%= 1000

total commision = 6000

What is the approciate formula ?

2 Replies

# Re: Problem with calculate commission

Hey, What I would use is nested IFS where you can specify your conditions in the following way: =IF("<Insert field of value here>"> 55.000, 5000*10%=500, IF("<Insert field of value here and insert the condition as it is not clear>", (50.000*10%)+(5.000*20%), "Some other case")) The formula might have some small typos but this is the gist of it. I hope it helps.

# Re: Problem with calculate commission

I'd use a small lookup range. Column H contains the difference between the commission of a tier and that of the previous tier.

The formula in B2 is

=SUMPRODUCT((A2>\$F\$2:\$F\$5)*(A2-\$F\$2:\$F\$5),\$H\$2:\$H\$5)

This can be filled down.