Profit Share Calculation

[Matthoff]

I am having a problem coming up with a way to calculate a variable profit sharing problem. Row 1 is profit month by month. In some months this is a negative number and in some months it is positive.

The cells in Row 2 needs to be zero if the entry above, in row 1, is negative. Row 2 needs to show 50% profit sharing until 2 million is reached in sharing when row 2 switches to 20% profit. The month (cell) when the switch from 50% to 20% occurs needs to account for a portion that reached 2 million at 50% and a portion above at 20%.

The 2 million in profit on row 2 can't include any of the negative profit cells.

If the threshold was 2500 instead of 2 million and row 1 contained:
-1000 -1000 1000 1000 1000 1000 2000 1000
Row 2 should contain:
0 0 500 500 500 500 700 200

[Hawkeye16]

If you want just formula based

Please Login or Register  to view this content.

[Matthoff]

Thank you very much. That was even nastier than I had imagined.