SOLVED

# help needed to build multi dimensional formula

Brass Contributor

# 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 are in form of MMDDYYYY

Now, I want the logic to always pull sales based on tracking start and end dates for DSeA first, second SCC, and lastly the SV engagement.

So, it should start pulling the sales for DSeA for month starting from February to October

Then for SCC it should just pull sales for Nov to Dec since there is an overlap in time periods, I don’t want the logic to double count sales for both the engagement type.

Lastly, for SV it should just pull sales for Jan 2023 as there is overlap in time periods for this engagement type as well.

Furthermore, the next check is the platform, once, I have TR pulled, then it should pull the other platforms JR and CM

The hierarchy will always be DSeA first based on start and end date next SCC and at last the SV engagement

Sales Tab:

5 Replies
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: 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.

# Re: help needed to build multi dimensional formula

@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!

# Re: help needed to build multi dimensional formula

@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.

# Re: help needed to build multi dimensional formula

@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!

# Re: help needed to build multi dimensional formula

@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.

1 best response

Accepted Solutions
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: 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.