Forum Discussion
Real Estate Commission formula help
I’m trying to build a worksheet to help me track my real estate business. I’ve gotten the worksheet to calculate most of my data but I’m having on issue. When I complete a sale I get a commission which I then have to share part of it with the Office. This commission is capped, Once I reach that cap I just pay a simple $100 fee to the office. this is what I need to happen.
This cell can have 3 results
- Commission * Split = Result
- =D2*$K$5
- Commission * Split + Sum of Current Commission > CAP = CAP-Sum
- =$J$2-SUM(E$2:E2)
- $100
- This will happen once the cap has been met.
This is the formula I have created:
=IF(SUM(E$2:E2)+D3*$K$5<=$J$2,D3*$K$5,IF(SUM(E$2:E2)+D3*$K$5>=$J$2,$J$2-SUM(E$2:E2),100))
The problem I have is that I’m getting either one of the 1st two IF results of a Zero result after the CAP has been met.
To add an additional variable I want this to track my yearly business results but my CAP is not based on the Calendar year. For example if I my CAP year starts 4/1 and I CAP in August the next year I’ll have to only pay the fee from January-March and then the CAP starts all over again. I have not started to try to figure this out yet. I wanted to get the basic CAP formula worked out first.
I’ve attached a sample spreadsheet that I’m using to work this out.
Hi Tim,
Perhaps
=IF(SUM(E$1:E2)+D3*$K$5<=$J$2,D3*$K$5,IF((SUM(E$1:E2)+D3*$K$5>=$J$2)*(SUM(E$1:E1)+D2*$K$5>=$J$2),100,$J$2-SUM(E$1:E2)))
and attached if I understood your logic correctly
- Tim MuradCopper Contributor
That did the trick. Now I'll see if I can make the adjustment for the change in calendar year.
Thanks!!!!
Tim