Excel split value equally until condition is met

[excel_user2001]

I think using the analogy of a gym will make my problem the easiest to understand. Say we own 2 gyms. At these gyms we provide personal training (PT) only in bundles of 5 sessions. These are booked online, and can be redeemed at either site.

My problem is that I want to account for the cash inflow as soon as someone makes an online booking BUT I do not know which site the sessions will be redeemed at. Therefore, I want to split revenue equally amongst the sites UNTIL a PT session is used up. At this point I know which site to attribute 1/5th (since we have a bundle of 5) of the revenue to, and the other 4/5ths can be split equally. This is then repeated until all of the sessions have been redeemed and revenue is apportioned accurately.

I have decent knowledge of excel but do not even know where to start with this. Would something like this even be possible on excel?

The only think i could think of which would be incredibly beefy, would be something along the lines of:
IFS( redeemed = 0, split rev. equally between sites, redeemed = 1, apportion 1/5th revenue to site which redeemed and the rest equally, ......)

If this is something that is possible I can provide sample data to make my question more clear, but didn't want to waste my time in case this is something out of Excels' abilities.

Thank you for any help!

 

[Herakles]

Lots of questions but please look at the image and pass commments.

Amount is split two ways for sessions not completed.

 

[excel_user2001]

Lots of questions but please look at the image and pass commments.

Amount is split two ways for sessions not completed.

Thank you so much for the response, that formula looks great for completing the initial split!

I think the crux of the problem here (for me anyway) is getting your formula to update as people redeem their sessions.
I have 3 sheets (see mini sheet below), and I want a formula for the highlighted yellow cells (revenue for each site), that updates as more entries are populated in the attendance data.

sales and attendance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Sales					Attendance					Revenue
2	Sale Date	Client ID	Item name	Price		Visit Date	Client ID	Item name	Site (1 or 2)		Site Name:	£
3	12/1/22	1	5 PT bundle	$ 50.00		12/1/22	1	5 PT bundle	1		Site 1
4	12/2/22	2	5 PT bundle	$ 50.00		12/2/22	2	5 PT bundle	2		Site 2
5						12/3/22	1	5 PT bundle	1
6						12/5/22	2	5 PT bundle	2
7						12/6/22	1	5 PT bundle	2
8						12/7/22	1	5 PT bundle	1
9						12/7/22	2	5 PT bundle	2
Sheet4

 

[Herakles]

It would be much easier to have a seperate worksheet for Sales, Attendance and Revenue.

You can add the D-G columns in my sheet to the Sales worksheet and add formulas as appropriate.

If you make the Attendance data a Table then adding in these formula will be easier.

Can I suggest that you have a Sales ID or Booking ID, in both the sales and Attendance data, as it the Booking row that needs to keep track of the Attendances and not the Client. A Client can have multiple Bookings over time. You can add a lookup formula to the Attendance table to show the Sales ID or Booking ID.

The formula in the Revenue sheet is just a simple SUMIF calculation on data in the Attendance sheet. Another reason to make it a table.
You may want to add in a start and end date so that you can compare data for periods. You could also do a pivot table on the Attendance sheet.