Need help with a split calculation based on date

Hi - thanks for reading and helping me out with this. I am a real estate broker and I am trying to get a better sense on income/expense management and pipeline revenue. I have built the attached sheet to be able to track the status of the deals I am working on and provide insight on potential, pending and earned commissions. I have been able to get everything working except for accurately calculating the split between brokerage vs broker commission from the deal. The split is based around reaching a $ split amount at which point the percentage share changes, it also resets on a given date each year. These parameters are indicated on the calculation page (note these numbers are not accurate for the industry but I am not allowed to accurately explain the exact split per the brokerage).

Where I need help.

On the transaction tab, under the brokerage section. Columns AI-AL are fine as they are just a simple percentage calculation that doesnt change against gross commission. Columns AM-AO is where I get stuck. These calculations change depending on the selected status in column B, and need to run against the representative dates of the transaction. Ie potential commission is against list date, Pending is against the contract pending date and closed is against the contract close date. Right now row 4 is just a simple calculation using the before split % found on the calculation tab as this transaction won't impact the split amount ($48,025 in this example). Subsequent rows however need to have a split calculation that assesses whether the brokerage split hits that target in a given transaction and then changes the revenue above that split from the before split % to the after split percentage. It also needs to take into account whether the transaction moves past the split date (found in yearly summary page) and if it does reset the split amount.

I'm sure running the calculation three times like this makes it much more difficult but it would provide me with very useful insight on my income based upon transactions closing near the split date.

Sidenote - I am building this in Google sheets so that I can use it on both my laptop and desktop. I have exported it to excel for this forum so some of the dropdowns (Status column on transactions) aren't working as intended.

Thank you

Thanks - I have now done this

The system won't let me attach links I thought maybe you could access it from the same link you had shared. Mods is there a way I can get the link included in my comments?

Note this is a repost of another thread so that I could include a link to a Google Sample Sheet


Hi - thanks for reading and helping me out with this. I am a real estate broker and I am trying to get a better sense on income/expense management and pipeline revenue. I have built the attached sheet to be able to track the status of the deals I am working on and provide insight on potential, pending and earned commissions. I have been able to get everything working except for accurately calculating the split between brokerage vs broker commission from the deal. The split is based around reaching a $ split amount at which point the percentage share changes, it also resets on a given date each year. These parameters are indicated on the calculation page (note these numbers are not accurate for the industry but I am not allowed to accurately explain the exact split per the brokerage).

Where I need help.

On the transaction tab, under the brokerage section. Columns AI-AL are fine as they are just a simple percentage calculation that doesnt change against gross commission. Columns AM-AO is where I get stuck. These calculations change depending on the selected status in column B, and need to run against the representative dates of the transaction. Ie potential commission is against list date, Pending is against the contract pending date and closed is against the contract close date. Right now row 4 is just a simple calculation using the before split % found on the calculation tab as this transaction won't impact the split amount ($48,025 in this example). Subsequent rows however need to have a split calculation that assesses whether the brokerage split hits that target in a given transaction and then changes the revenue above that split from the before split % to the after split percentage. It also needs to take into account whether the transaction moves past the split date (found in yearly summary page) and if it does reset the split amount.

I'm sure running the calculation three times like this makes it much more difficult but it would provide me with very useful insight on my income based upon transactions closing near the split date.

https://docs.google.com/spreadsheets...it?usp=sharing

Thank you