hello,I have a similar question, could you please help me?
correct formula:
LET(
s_list, CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13}, BT4,BY4,CD4,CI4,CN4,CS4,DC4,DH4,DM4,DR4,DW4,EG4,EL4),
days_list, CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13}, BW4,CB4,CG4,CL4,CQ4,CV4,DF4,DK4,DP4,DU4,DZ4,EJ4,EO4),
cumulative_S, SCAN(0, s_list, LAMBDA(a, v, a + v)),
total_days, SUM(days_list),
break_week, IFERROR(MATCH(TRUE, cumulative_S >= BK4, 0), 14),
remaining_S, BK4 - IF(break_week > 1, SUM(INDEX(s_list, SEQUENCE(break_week - 1, 1, 1, 1))), 0),
partial_days, IFERROR((remaining_S / INDEX(s_list, break_week)) * INDEX(days_list, break_week), 0),
IF(
break_week <= 13,
ROUND(
IF(
break_week = 1,
0,
SUM(INDEX(days_list, SEQUENCE(break_week - 1, 1, 1, 1)))
) + partial_days,
0
),
ROUND(total_days, 0)
)
)
When using java POI to generate a formula, a lot of @ symbols are added. I am currently using Excel 365