Forum Discussion
Help with weekly data project
Hi! I work with attendance data every week and don't know much about Excel or how I can make things quicker and easier. I work with attendance data, which comes from our ticketing service in three co...
Hi, enclosed you will find a solution for your task. This is also suitable for writing a user-defined function as LAMDA. The function fances() is defined in the name manager in the attached example.
=LET(f_amoti,FILTER(A1:A5,C1:C5=E1),f_zipc,FILTER(B1:B5,C1:C5=E1),XLOOKUP(SEQUENCE(SUM(f_amoti)),SCAN(0,f_amoti,LAMBDA(a,b,SUM(a,b))),f_zipc,"",1))=LAMBDA(amoti,zipc,dow,f_day,LET(f_amoti,FILTER(amoti,dow=f_day),f_zipc,FILTER(zipc,dow=f_day),XLOOKUP(SEQUENCE(SUM(f_amoti)),SCAN(0,f_amoti,LAMBDA(a,b,SUM(a,b))),f_zipc,"",1)))=frances(A1:A5,B1:B5,C1:C5,H1)
In summary, the formula filters two ranges (A1:A5 and B1:B5) based on a condition (values in C1:C5 equal to E1), generates a cumulative sum of the filtered values in A1:A5, and uses these sums as lookup criteria to find corresponding values from the filtered values in B1:B5. The formula then returns a list of the found values.
