Calculate a persons money share based on when they join/leave a syndicate

Hello,

I am improving a spreadsheet for our office lottery syndicate (spreadsheet attached). I have gotten so far but now stuck.

What I am looking to acheive is, calulate when a person joined the sydicate and give them a share of winnings based on the time in which they started till the time they leave/last paid. The 'P' represents a person has paid till that date.

For instance 'Chris' in B21, joined on 29/7/19 and has paid till 19/8/19. He joined the syndicate late so will only take winnings from the date which he started; so £30 divided by the number of current players, which would mean he would receive £1.87 in total for the year. Whereas Kath would have received £22.58 as she has been in longer.

I have a totals page which needs to show these calulalations for each player but unsure how to approach it.

Any questions please ask.

SOLVED

In Totals!D5, enter:

Formula:

Please Login or Register  to view this content.

and copy down.