Adding a new member (lottery syndicate)

[tcbflyr]

Hi everyone.
I've just joined and I'm needing your help. I've just created an excel spreadsheet to calculate the share of lottery winnings for 20 people (syndicate). And have managed to readjust the share and total if someone leaves. But how do I add someone without giving them a full share of money already accumulated.
Hope this makes sense.

Jim

[José Augusto]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Thanks.

[tcbflyr]

As you can see from the spreadsheet. I have a Left column O5, this is used when someone leaves. I add their share to this column and reduce the players by one
. Probably not the most efficient way, but I'm relatively new to excel. What i want to be able to do is add someone at a latter date and not have them
being entitled to the current members share. But a share based on future winnings. A26 where i would add them. Hope this makes sense.

[José Augusto]

Hello

Thank you for submitting the file.
I made some changes to the layout to make clear the exit procedure, with some doubts about what to do when two or more come out.

As for the entry of another bettor to the union I think it's settled.
I entered a column to record the status of each ticket holder (In, Out or nothing). Excel proceeds counts and updates the premium of each.

See if this is what you intended.

[tcbflyr]

Thank you for your help José Augusto. I like what you have done to the spreadsheet, but the issue i have is that when you have a total of
£100 between 20 players and one leaves. Their share should be £5.00 and the rest of the 19 players should have £5.00. What seems to be happening
is that Me and me is leaving with £2.56 and the other 19 players are getting £5.13
And lets say next week has a £10 win. Me and Me's leaving total increases. Can i also add someone where their winnings are proportional to the money won ater they have joined.

[José Augusto]

Hi
The formulae in N6, O6 are

Please Login or Register  to view this content.

Is that correct?
See the file

[tcbflyr]

It's calculating the right amount when leaving now. But if you add another £5 to L21 for example you increase the O17 by £0.25 and increase the share amount by the same.
O17 should not be receiving any further amounts after leaving.

[José Augusto]

Hello

I created two new sheets, 'Week_reg' and Values.

The 'Values' sheet will automatically record the weekly values.

On the Week_reg sheet you will have to fill in the weekly Euro Millions key and the total prize. It will also register the ticket holders and the outputs of some element.

See the file

[tcbflyr]

Hi José

I'm a bit confused now. Am I now adding the euro millions numbers in the week_reg instead of the Ostlers Euromillions. Thank you for all your help. It's greatly appreciated.

[José Augusto]

Hi @tcbflyr

Sorry to have confused you. I'll try to explain better with an example and a more complete new version of the spreadsheet.

I use the 'Week_Reg' sheet in w_3 to set the EuroMillions key and stars and register who is IN, OUT or leave the group (null).

This sheet calculates the Prize Fund and the Share after you in cell F4 inform the amount of the prize calculated in the sheet 'Ostlers EuroMillions'.

In the 'Ostlers EuroMillions' sheet you can see who is 'IN' 'OUT' or left the group, as well as the key and stars.

You can use the range D8: K42 to display the keys of all the elements that are 'IN' and their Award value.

The M7 cell sums these values ​​and the N4 cell alerts you to do the update in Week _Reg! F4. When you do this, cell N4 compares the value in M7 with the 'Week _Reg'! F4 to validate.

See the file

[tcbflyr]

Thank you for all your help. I finally got my head around it. Just one more question though. If New_Guy_1 and New_Guy_2 joined in week two should their share of the prize fund not be smaller than those people that have been here from week one?

[José Augusto]

Hi

A new sheet 'Options' is added. You can establish what % you want for each status (IN, OUT, 1.st). For example 100%, 100%, 60%.

As you see, another status is added and the formulas must be updated.
In 'Week_reg' sheet, the formula for the field share calculus is (in D4 from D4 to W4, but you can extend that)

Please Login or Register  to view this content.

Note: In the example, New_guy_1 is 'IN' and New_guy_2 is '1.st'
See the new file