Forum Discussion
help needed to build multi dimensional formula
AKuma0411 I'm not sure how well this will perform with a larger dataset, but you can give it a try anyways...
=LET( ucn, A2:A16, type, C2:C16, platform, E2:E16, start, F2:F16, end, G2:G16, months, Sales!E1:P1, data, Sales!E2:P10, ucns, Sales!A2:A10, platforms, Sales!C2:C10, MAP(ucn, type, platform, start, end, LAMBDA(u,t,p,s,e, LET( incl1, (months>=s)*(months<=e), incl2, IF(t<>"DSeA", NOT((months>=MINIFS(start, ucn, u, type, "DSeA", platform, p))*(months<=MAXIFS(end, ucn, u, type, "DSeA", platform, p)))*incl1, incl1), incl3, IF(t="SV", NOT((months>=MINIFS(start, ucn, u, type, "SCC", platform, p))*(months<=MAXIFS(end, ucn, u, type, "SCC", platform, p)))*incl2, incl2), IFERROR(SUM(FILTER(FILTER(data, (ucns=u)*(platforms=p)), incl3, 0)), 0)))) )MAP Function
If there's too much of a lag when applied to a larger dataset, try the second workbook (see attached), which generates a report based on the selected range of UCN's (but keep the range to a reasonable number... if too many are selected, it will also perform poorly).
Note: these will only work with Excel for MS365 or Excel for the Web.
AKuma0411 I'm not sure how well this will perform with a larger dataset, but you can give it a try anyways...
=LET(
ucn, A2:A16, type, C2:C16, platform, E2:E16, start, F2:F16, end, G2:G16,
months, Sales!E1:P1, data, Sales!E2:P10, ucns, Sales!A2:A10, platforms, Sales!C2:C10,
MAP(ucn, type, platform, start, end, LAMBDA(u,t,p,s,e, LET(
incl1, (months>=s)*(months<=e),
incl2, IF(t<>"DSeA", NOT((months>=MINIFS(start, ucn, u, type, "DSeA", platform, p))*(months<=MAXIFS(end, ucn, u, type, "DSeA", platform, p)))*incl1, incl1),
incl3, IF(t="SV", NOT((months>=MINIFS(start, ucn, u, type, "SCC", platform, p))*(months<=MAXIFS(end, ucn, u, type, "SCC", platform, p)))*incl2, incl2),
IFERROR(SUM(FILTER(FILTER(data, (ucns=u)*(platforms=p)), incl3, 0)), 0))))
)
MAP Function
If there's too much of a lag when applied to a larger dataset, try the second workbook (see attached), which generates a report based on the selected range of UCN's (but keep the range to a reasonable number... if too many are selected, it will also perform poorly).
Note: these will only work with Excel for MS365 or Excel for the Web.
djclements Thank you for your response! In the first method, for the line item 8 it should pull sales for first 6 months and not Jan and feb sales, I there a easy way to tweak this logic ?
Sheet 1:
Sales Tab:
Thanks again!
AKuma0411 I have to say, I believe that would contradict your hierarchy logic... you said "DSeA" first, "SCC" second and "SV" third. In line 7, "SCC" for platform "MS" (March to December) takes precedence, does it not? Because of the overlap, "SV" only gets January and February. At least that was my understanding... let me know.
djclements yes, that's correct. I didn't notice it was for the same platform "MS". This is exactly the order in which the logic should pull sales. this is perfect!! I want to perform this operation on approx. 20,000 rows of data, will it work with that kind of range?
Thanks!
