The Problem:
Revenues are received monthly and then distributed to two different parties at percentages that change.
1. At first, 75% of the revenues will go to Mark and 25% to Frank.
2. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s initial investment of $1000 is paid back.
3. Once both parties have been compensated equally the percentage will change one last time to 50% Mark and 50% Frank.
I also need to be able to see when the percentages convert.
My solution so far:
So far this is the formula I have created. This formula does two main things. It checks to see if the total amount of the revenue column up to the current month is less then or greater the $1000. If it is less then it performs the calculation of multiplying the revenue for that month by 75%. If it is greater than the $1000 then it calculates the difference and only multiplies the difference by 75%.
=IF(SUM(B$4:B5)<C$1,B5*$C$2,((SUM(B$4:B5))-C$1)*C$2)
The problem with the formula is that it keeps calculating at 75% even after the $1000. I’m not sure how to get it to convert to 25% in the middle of the month.
I have thought about creating separate columns for calculating each percentage. I may be going about this completely wrong so I am completely open to a new spreadsheet layout and/or formulas for solving the problem.
Thanks
Texan Fan
using Window 7, Excel 2007
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mark's Investment
[/TD]
[TD]$1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]75%
[/TD]
[TD]25%
[/TD]
[TD]50%
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]Revenue
[/TD]
[TD]Marks Amount
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]250
[/TD]
[TD]Place formula here
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</C$1,B5*$C$2,((SUM(B$4:b5))-c$1)*c$2)[>
Revenues are received monthly and then distributed to two different parties at percentages that change.
1. At first, 75% of the revenues will go to Mark and 25% to Frank.
2. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s initial investment of $1000 is paid back.
3. Once both parties have been compensated equally the percentage will change one last time to 50% Mark and 50% Frank.
I also need to be able to see when the percentages convert.
My solution so far:
So far this is the formula I have created. This formula does two main things. It checks to see if the total amount of the revenue column up to the current month is less then or greater the $1000. If it is less then it performs the calculation of multiplying the revenue for that month by 75%. If it is greater than the $1000 then it calculates the difference and only multiplies the difference by 75%.
=IF(SUM(B$4:B5)<C$1,B5*$C$2,((SUM(B$4:B5))-C$1)*C$2)
The problem with the formula is that it keeps calculating at 75% even after the $1000. I’m not sure how to get it to convert to 25% in the middle of the month.
I have thought about creating separate columns for calculating each percentage. I may be going about this completely wrong so I am completely open to a new spreadsheet layout and/or formulas for solving the problem.
Thanks
Texan Fan
using Window 7, Excel 2007
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mark's Investment
[/TD]
[TD]$1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]75%
[/TD]
[TD]25%
[/TD]
[TD]50%
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]Revenue
[/TD]
[TD]Marks Amount
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]250
[/TD]
[TD]Place formula here
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</C$1,B5*$C$2,((SUM(B$4:b5))-c$1)*c$2)[>