Forum Discussion

red_line's avatar
red_line
Copper Contributor
Nov 15, 2023

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_line's avatar
      red_line
      Copper Contributor

      HansVogelaar 

      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

Resources