Revenue Distribution Formula

[Texan Fan]

Revenues are received monthly and then distributed to Mark and Frank.

At first, 75% of the revenues will go to Mark and 25% to Frank. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s $1000 investment is paid back. Once both parties have been compensated equally the percentage split will convert to 50%/50%. I also need functionality that tracks the money to alert me when the percentages should convert.

What formula should I use?

Month	Revenue	Mark's 75% / 25% / 50%​	Frank's 25% / 75% / 50%​
Jan	250	formula​	formula​
Feb	300	formula	formula
March	300
April	350

<tbody>
</tbody>

Thanks in advance for any tips.
Texan Fan
I'm using Excel 2007 and Windows 7

 

[Robert Mika]

Revenues are received monthly and then distributed to Mark and Frank.

At first, 75% of the revenues will go to Mark and 25% to Frank. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s $1000 investment is paid back. Once both parties have been compensated equally the percentage split will convert to 50%/50%. I also need functionality that tracks the money to alert me when the percentages should convert.

What formula should I use?

Month	Revenue	Mark's 75% / 25% / 50%​	Frank's 25% / 75% / 50%​
Jan	250	formula​	formula​
Feb	300	formula	formula
March	300
April	350

<tbody>
</tbody>

Thanks in advance for any tips.
Texan Fan
I'm using Excel 2007 and Windows 7

Hello and welcome:

Excel 2010
	A	B	C	D	E
1	Month	Revenue	Mark's 75% / 25% / 50%	Frank's 25% / 75% / 50%
2
3	Jan	250	187.5	62.5	250
4	Feb	300	337.5	212.5	550
5	March	300	487.5	362.5	850
6	April	350	575	625	1200
Sheet1

Cell Formulas
Range	Formula
C3	=IF(SUM($B$3:B3)<500,C2+B3*0.75,IF(SUM($B$3:B3)<1000,C2+B3*0.5,C2+B3*0.25))
D3	=IF(SUM($B$3:B3)<500,D2+B3*0.25,IF(SUM($B$3:B3)<1000,D2+B3*0.5,D2+B3*0.75))
E3	=SUM($B$3:B3)

 

[Texan Fan]

Thank you for the quick reply Robert. That was my first post on the forum and I appreciate the help. As I continue to work on the spreadsheet I may need to post another question.

Thanks again,
Texan Fan

 

[Robert Mika]

Thank you for the quick reply Robert. That was my first post on the forum and I appreciate the help. As I continue to work on the spreadsheet I may need to post another question.

Thanks again,
Texan Fan

You are welcome.
You can be sure that someone will answer your next question.

 

[jaci50]

Thank you robert it will help in my assignment just because of the solution you provide. I am trying to found out this formula now i got it. revenues.co.uk

 

[Robert Mika]

Thank you robert it will help in my assignment just because of the solution you provide. I am trying to found out this formula now i got it. revenues.co.uk

You are welcome.