Forum Discussion
Calculating revenue split with caps
Hi all. Been trying to crack this problem for a few hours but struggling. Hopefully someone can point me in the right direction.
I'm trying to calculate the revenue split between two partners, but with certain conditions on how it is shared.
Here is the scenario.
Column A: revenue per period is $120 over a total of 24 periods
Those revenues are shared between the two partners; Tom and Jerry in the next two columns (B and C). I need formulas for columns B and C that distribute the revenue to the two partners according to the following logic:
Rule 1: The first $250 of all revenue earned goes to Tom. Jerry gets nothing until Tom has earned $250
Rule 2: Subsequent revenue is shared equally between Tom and Jerry (50% to each)
Rule 3: Once the share of revenue received by Tom reaches $1,000 then ALL subsequent revenue goes only to Jerry.
Obviously I would like to play with the three variables that determine the revenue distribution (a threshold of (=$250) below which Jerry earns nothing, a cap on Toms earnings (=$1,000), and a revenue share proportion (50%) when Tom's share is between those two values. So I don't want to hardcode these, but have them as inputs.
Any help is much appreciated!!!
3 Replies
- red_lineCopper Contributor
Many thanks for this! Worked a charm.
Its an elegant solution, and need to work through it to fully understand the logic and syntax you used.
I might come back for some clarifications (if you don't mind) as I am really keen to add to my toolbox, but meantime I really appreciate the solution.
Cheers - red_line
Feel free to ask more questions!