Help needed - IF Statements, trying to calculate commissions

Hi,

I am trying to calculate commissions at various levels and am new to IF statements, after trying several times I thought I had it solved, but I just cant get it to work properly.

I just need a fully working example for one and I can do the rest!

Can anyone help?

Spreadsheet is attached.

Basically I just want to get row 39 working properly, so for this instance we only calculate it using Rows 21,22,23.

You take the fee billed (B26) and then for the various tiers you work out how much you earnt.

Fee = $30,000

The 1st tier; $0-$4,000 you get 0% on, so nothing.
The 2nd tier; $4,001 - $12,000, you get 45% on. So $7,999 * 45% = $3,599.55
Then you still left over $30,000 - $12,000 = $18,000 which gets calculated against the highest tier at 50% = $9,000

so total comm's = $9,000 + $3599.55 = $12599.55

but if your fee = 0, then comm's should = 0.

Anyways I have tried heaps of different ways to get it to work, i got the highest tier working, then the second and even the third. but if i just put 0 i was getting a negative returned.

Help please!

Hi,

You are looking at rows 6-9, which i would try and solve in B36 - you can solve that if you want, but it is slightly more complicated.

I was trying to solve B39, which you use Rows 21,22,23 for.

The thresholds are basically over the course of the year as you earn money for the company, the commission you earn on the fee's you brought in goes up.

So for the first $200k you earn the business you earn Tier 1 levels of comm's. Then $201k - $400k, you earn Tier 2 level comm's, and it increases again $401 - $500k, then aything over $500k at the highest tier.

then within each Tier the comm's are calculated as per columns B & E, at the % in G. F is simply the amount between the levels to make it easier to calculate in a formula.

does that make sense?

if you think a vlookup is easier / works better please let me know as well, i am open to any solution as long as it works

so yeah - i just want a working solution for B36, B37, B38 or B39. Any would be fine.

thanks!