# Help needed - IF Statements, trying to calculate commissions

1. ## 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?

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.

2. ## Re: Help needed - IF Statements, trying to calculate commissions

Hi and welcome to the forum

A few of the things you say/show dont make any sense (to me at least)...
The 2nd tier; \$4,001 - \$12,000, you get 45% on
Yet your table shows 20% for this range? how do you get 45%?

Also, I dont understand how the Threasholds column B:C fid into this?

If you set the tables up properly, you should be able to use vlookup() instead of nested IF()'s

3. ## Re: Help needed - IF Statements, trying to calculate commissions

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?

4. ## Re: Help needed - IF Statements, trying to calculate commissions

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!

5. ## Re: Help needed - IF Statements, trying to calculate commissions

I will have to take a look at this a bit later, my time is up here for now

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1