Forum Discussion

AKuma0411's avatar
AKuma0411
Brass Contributor
Mar 06, 2024
Solved

help needed to build multi dimensional formula

Hi everyone, I need help building a logic in excel, here’s what I’m trying to do: I have two tabs in excel, sheet 1 and the other is sales tab,  In sheet 1, I have following data points: The dates...
Formulas and Functions
  • djclements's avatar
    djclements
    Mar 06, 2024

    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's avatar
djclements
Bronze Contributor
Mar 07, 2024

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.

AKuma0411's avatar
AKuma0411
Brass Contributor
Mar 07, 2024

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! 

  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 08, 2024

    AKuma0411 I just tested it with 20,000 rows of data on Sheet1, and 11,430 rows of Sales data. On average, it processed approx. 800 rows of data per second (25 seconds to complete). Results may vary, depending on your processor speed, available RAM, etc. This isn't the type of formula that you would want to be "live" at all times... you could use it to spill the results, then promptly select the entire results range and Copy > Paste Special > Values.

     

     

Resources