A LET allows you to define intermediate results, and use those results in later calculations. So it's just a matter of keeping track of what you need to do.
=LET(start,A2,nd,B2,hol,D2:D20,hold,E2:E20,s,SEQUENCE(nd*2,,start,1/2),m,MOD(s,1),f,FILTER(s,(m=0)+((WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IFERROR(XLOOKUP(INT(f),hol,hold,,0),TEXT(f,"dddd"))&"/"&IFS(ISNUMBER(MATCH(INT(f),hol,0)),"BH",MOD(f,1)=0,"1",1,"2/3"),CHOOSE({1,2},INT(f),d))
start,A2,nd,B2,hol,D2:D20,hold,E2:E20,
This part just gets the values from the sheet. You can name them anything that is meaningful to you, here "start" is the start date, "nd" is number of days, hol is holidays, hold is holiday day.
s,SEQUENCE(nd*2,,start,1/2),
This part creates a list of numbers starting with the start date, and going up by 1/2. So if the start date is 65649, then it goes 65649, 65649.5, 65650, 65650.5, etc. The numbers with .5 will be the 2/3 instance for each day.
m,MOD(s,1),
MOD divides the number (s) by the divisor (1) and returns the remainder. Since it's being applied to the range we just defined, it returns 0,.5,0,.5, etc.
f,FILTER(s,(m=0)+((WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0))))
Now we want to remove out the 2/3 instances from any weekends or holidays. This FILTER basically says to keep anything in s where the remainder is 0 (red), which we already established is the 1st instance for each day, and also keep anything that is a weekday (blue) that isn't a holiday (green).
d,IFERROR(XLOOKUP(INT(f),hol,hold,,0),TEXT(f,"dddd"))&"/"&IFS(ISNUMBER(MATCH(INT(f),hol,0)),"BH",MOD(f,1)=0,"1",1,"2/3"),
We now have a list of the instances, so we need the descriptions. So we look up to see if the number in the filtered list (f) is a holiday (red). We use INT to remove the .5 if needed. (Although now that I think about it, we already removed the .5 instances for all holidays, so it's not necessary.) If it's a holiday, we get the holiday name from hold. If it's not, we get an error, and the IFERROR uses TEXT to put the day of the week (blue). We then add a /. We check again for a holiday (red again) and put BH if so. Otherwise we see if the remainder is 0 (blue) and put 1 in, otherwise we put 2/3 because it must be the 2/3 instance on a weekday (green).
CHOOSE({1,2},INT(f),d))
Finally we use CHOOSE to put the filtered list f next to the description d. (HSTACK would work too.)
=MOD(N(I1),C$2)+1
The MOD formula is pretty easy. Since we combined the 2/3 instances into 1 row, all we need to do is add 1 to the number from the previous row. The N() is used to turn the "Person No" heading to a 0 and doesn't affect anything else. Since we want to wrap when we get to 15, we could do something like: =IF(I1=C$2,1,N(I1)+1) which might be clearer. But remember MOD takes the remainder when dividing by C$2, so for every number <15, it just returns that number. When it gets to 15, MOD will return 0, so adding 1 to it will give you 1.
The team formula relies on the formulas in S and T. The S formula figures out how many rows (instances) are in column G, then figures out how many times each team should be assigned based on the percentage in R. The T formula is the sum of each row as you go down the table. If you take the header row as 0, then you can use T1:T8 as a table for MATCH. If you take an array, 0 to the number of rows in G-1, and give that array to MATCH using T1:T8, you'll get an array with 74 1's, 59 2's, 44 3's, etc.
=LET(a,ROWS(G2#),m,IFERROR(MATCH(SEQUENCE(a,,0),T1:T8),1),INDEX(P2:P8,SORTBY(m,RANDARRAY(a))))
So this formula just takes an array from 0 to rows in G - 1 (red), gives it to MATCH (blue), to get an array m with 74 1's, etc. then sorts that array by a random order (green), and finally uses INDEX (purple) to get the team names. The formula in U is not really needed, I just wanted to make sure that I got back the right values. Note that since it uses RANDARRAY, the list will resort if you change this sheet. So if you use this formula at the start of the year, you'll need to print it out, or convert the formula to values to prevent it from changing.
Hope this helps!
