Commission Calculation Spreadsheet

i'm a new real estate agent so I need to track my sales and commissions.

I have variables, so if it is an Auction, Promotional Sale, Sole Agency or General Agency there are different commission splits.

It gets complicated:

Commission Splits:

The commissions split differently depending on the type of listing and whether you listed it, sold it or listed and sold it.If I list a sold property.

e.g. Auction list 65%, Sell 35%, Promotional list 50% sell 50%, Sole list 40%, sell 60%, General list 20% sell 80%, Other list 70% sell 30%

If I List and sell a property, regardless of listing type I get 100% of the commission

Admin Fees

We are charged a $500 admin fee for each sale. This is obviously split between the listing and selling agents on the same basis as the commission splits above.

However the admin fee is only extracted after the commission is calculated.  Sop for example if a property sells at auction for $300,000. The company charge a commission of 3.95% or $11850.  That $11850 is added to my annual commission build as per the previous formula, but extracted in my payout.  So my annual commission build increases by $11850, but the payout to the listing and selling agent is $11350.  If I was on 50% my commission build would show $5925, but my commission earned would be $2837.50.

So that means I need to set a few more formulas.

1. a cell that calculates: 

IF G2 = "LIST/SELL" THEN THIS CELL EQUALS H2, IF G2 DOES NOT EQUAL "LIST/SELL" THEN THIS CELL EQUALS H2/2

2. My company charges a commission of 3.95% on the first $300,000 then 2% on the amount above that, WITH A MINIMUM FEE OF $8000. So I need a cell that calculates: 

IF E2 IS EQUAL O OR LESS THAN $300,000 THEN THIS CELL IS E2*0.0395 ALTHOUGH A MINIMUM OF $8000.  IF E2 IS GREATER THAN $300,000 THEN $300,000*0.395 PLUS THE AMOUNT GREATER THAN $300,000 *0.02

3. This is where it starts to get complicated, a cell that calculates:

IF F2= AUCTION and G2= LIST THEN J2*0.65

IF F2= AUCTION and G2= SELL THEN J2*0.35

IF F2= SOLE and G2= LIST THEN J2*0.4

IF F2= SOLE and G2= SELL THEN J2*0.6

IF F2= PROMOTIONAL and G2= LIST THEN J2*0.5

IF F2= PROMOTIONAL and G2= SELL THEN J2*0.5

IF F2= GENERAL and G2= LIST THEN J2*0.2

IF F2= GENERAL and G2= SELL THEN J2*0.8

IF F2= CONJUNCTIONAL and G2= LIST THEN J2*0.7

IF F2= CONJUNCTIONAL and G2= SELL THEN J2*0.3

IF F2= AUCTION CONJUNCTIONAL and G2= LIST THEN J2*0.8

IF F2= AUCTION CONJUNCTIONAL and G2= SELL THEN J2*0.2

IF G2 = LIST AND CELL THE THIS CELL EQUALS J2

4.  The formula that you provided if fine, it will take it's calculation from no.3 above. But the one to the left, in your formula which in my full sheet is L, needs to understand that the $500 admin fee has to be deducted on the commission paid. Sop rather than being a formula that is M3-M4. it needs to consider the same variable as above on the $500. So for example a company sale of a property with a commission of $8000, at auction in which I was the listing agent would provide me a company commission of $5200 (65% of $8000), but rather than a 50% pay out of $2600, I would get paid (($8000-$500)*.65)*.5) $2437.50.

Would it therefore make sense for this cell to be separate from a link to column M, with a similar formula to the one you provided, based on the calculation in item 3 above, but with the $500 excluded?

I have the base spreadsheet in the following link: https://www.dropbox.com/sh/vf3nljiyo7u7ufz/AADHuXD7FPblq-vhElBoAka0a?dl=0

I hope some of this question makes sense?

Cheers

Mike