Real Estate Commission with Cap Formula

[agentbarker]

Hello All!

Via some google searches, I found a few similar questions answered by you fine people but didn't see my exact question and couldn't tweak on my own. Here is the scenario:

Agent has an 80/20 split with the brokerage. If they sell 100000.00 house, they get 3% commission (3000) and give the broker 20% (600). They would then keep the 2400. This will continue throughout the calendar year. Once they've earned enough commission to pay the broker 15000.00, they no longer pay the broker 20%. They then only pay 100.00 per transaction. So once they earn 75000 in gross commissions, they only pay 100.00 after that.

I need a formula that adds until it hits 15k and then adds 100.00 for each additional row?? (I think)
Let me know what you guys think. Thanks SO much in advance!

Here's a link to the google sheet:
https://docs.google.com/spreadsheets/d/1pw4Ys3nqW5LoAYO1HJvenULv4pWN1WGfHoRf5u8omFY/edit?usp=sharing

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

try this in F3 and copy down

=IF(SUM($E2:E$3)>=15000,F2+100,SUM($E$3:E3))

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

You sir, are a champ. Worked like one. So happy with a prompt response for my first post here! Good day sir!

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

try this in F3 and copy down

=IF(SUM($E2:E$3)>=15000,F2+100,SUM($E$3:E3))

Well, one more question:

Please look at the sheet again. Is there a way to stop F from populating when there isnt a selling price in B? That way an agent could use the sheet as they go without it autopopulating just because there's a formula in the cell?

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

you're welcome

try this

=IF(ISBLANK(B3),"",IF(SUM($E2:E$3)>=15000,F2+100,SUM($E$3:E3)))

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

AND CLOSED!
Seriously, thank you. I'll be back!

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

AND CLOSED!
Seriously, thank you. I'll be back!

Seriously? LOL.

Ok, so how do I get 'E1' to return the last value in column E (not including e1)
Will also do the same for 'J'

I tried googling about indexes and countA but couldnt get those to display a number. Is that because the numbers in e and j are resulting from a formula?

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

and i dont know how to edit a post here...
https://docs.google.com/spreadsheets/d/1pw4Ys3nqW5LoAYO1HJvenULv4pWN1WGfHoRf5u8omFY/edit?usp=sharing

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

easiest option is

=MAX(E3:E1000)

similar for J

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

I've realized a problem. After the 15000 cap has been reached, the agent then keeps 100% commission but still pays 100.00 to the broker. I've highlighted on the table where that would occur but dont know how to build that into the complex if(then formula.
https://docs.google.com/spreadsheets/d/1pw4Ys3nqW5LoAYO1HJvenULv4pWN1WGfHoRf5u8omFY/edit?usp=sharing
that's a link to edit the sheet in sheets.