MP

MP(n,a,b,[c],[d])
n
array's index nr., 1,2,3 or 4
a
array
b
array
[c]
array, if omitted n can be 1 or 2, not omitted n can be 1,2, or 3
[d]
array, if omitted n can be 1,2 or 3, not omitted n can be 1,2,3 or 4
MAP Expander
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
MP MAP's multiple arguments Expander.
MP can expand all its array arguments into arrays that will have same dimensions, and index them using "n" .
If initial array arguments are MAP friendly, the expander will turn them into MAP proper arrays.
MP can enhance any MAP expression that deals with multiple array arguments, to work with MAP friendly arrays of different dimensions.
syntax example that reflects MP functionality
F(x,y,z)=........MAP(x, y, z, LAMBDA(......
if x,y,z are arrays of different dimensions MAP(x,y,z will not work. => will use MP
F(x,y,z)=......MAP(MP(1,x,y,z),MP(2,x,y,z),MP(3,x,y,z),LAMBDA(.......
Excel Formula: 
=LAMBDA(n, a, b, [c], [d],
    LET(
        w, MAX(ROWS(a), ROWS(b), IFERROR(ROWS(c), 0), IFERROR(ROWS(d), 0)),
        l, MAX(COLUMNS(a), COLUMNS(b), IFERROR(COLUMNS(c), 0), IFERROR(COLUMNS(d), 0)),
        p, LAMBDA(x, IFNA(EXPAND(x, w, l), x)),
        SWITCH(n, 1, p(a), 2, p(b), 3, p(c), 4, p(d))
    )
)
MAPS.xlsx
ABCDEFGHIJKLMN
1Concept. MAP friendly arrays explained using DATE function.
2
3All arguments in DATE(y,m,d) can deal with arrays.
4Any array distribution that makes DATE to deliver consistent result => are good examples of MAP friendly arrays
5By default, any arrays that have same dimensions are MAP friendly.
6examples of MAP friendly arrays:
7
8ymd=DATE(B9:B11,D9:F9,H9)
920208910101-08-2001-09-2001-10-20
10202101-08-2101-09-2101-10-21
11202201-08-2201-09-2201-10-22
12
13
14ymd=DATE(B15,D15:F15,H15:H18)
1520228910101-08-2201-09-2201-10-22
16202-08-2202-09-2202-10-22
17303-08-2203-09-2203-10-22
18404-08-2204-09-2204-10-22
19
20
21ymd=DATE(B22:B24,D22:D24,H22)
2220208101-08-20
2320219201-09-21
24202210301-10-22
25
26
27ymd=DATE(B28:B30,D28,H28)
28202091515-09-20
29202115-09-21
30202215-09-22
31
32
33ymd=DATE(B34:B37,D34:F34,H34:H37)
3420208910101-08-2001-09-2001-10-20
352021202-08-2102-09-2102-10-21
362022303-08-2203-09-2203-10-22
372023404-08-2304-09-2304-10-23
38
39
40ym=DATE(B41:B44,D41:F41,D44:F44)
412020891001-08-2002-09-2003-10-20
42202101-08-2102-09-2103-10-21
432022d01-08-2202-09-2203-10-22
44202312301-08-2302-09-2303-10-23
45
46
47ymd=DATE(B48:C50,E48:F50,H48:I50)
48201820217101201-07-1802-10-21
49201920218113403-08-1904-11-21
50202020229125605-09-2006-12-22
51
52
53examples of MAP unfriendly arrays
54
55ymd=DATE(B56:B58,D56:F56,H56:H57)
5620208910101-08-2001-09-2001-10-20
572021202-08-2102-09-2102-10-21
582022#N/A#N/A#N/A
59
60
61ymd=DATE(B62:B65,D62:F63,H62:H65)
622020678101-06-2001-07-2001-08-20
6320218910202-08-2102-09-2102-10-21
6420223#N/A#N/A#N/A
6520234#N/A#N/A#N/A
66
MP 1
Cell Formulas
RangeFormula
K8,K61,K55,K47,K40,K33,K27,K21,K14K8=FORMULATEXT(K9)
K9:M11K9=DATE(B9:B11,D9:F9,H9)
K15:M18K15=DATE(B15,D15:F15,H15:H18)
K22:K24K22=DATE(B22:B24,D22:D24,H22)
K28:K30K28=DATE(B28:B30,D28,H28)
K34:M37K34=DATE(B34:B37,D34:F34,H34:H37)
K41:M44K41=DATE(B41:B44,D41:F41,D44:F44)
K48:L50K48=DATE(B48:C50,E48:F50,H48:I50)
K56:M58K56=DATE(B56:B58,D56:F56,H56:H57)
K62:M65K62=DATE(B62:B65,D62:F63,H62:H65)
Dynamic array formulas.
 
Upvote 0
MAPS.xlsx
ABCDEFGHIJKLM
1Concept. MAP proper arrays.
2
3from MAP friendly arrays to MAP proper arrays
4
5ymd
6202089101
720212
820223
9
10MAP can not handle MAP friendly arrays as they are, like DATE can. (only if they have same dimensions).
11
12=MAP(B6:B8,D6:F6,H6:H8,LAMBDA(y,m,d,DATE(y,m,d)))
1344044#N/A#N/A
14#N/A#N/A#N/A
15#N/A#N/A#N/A
16
17from MAP friendly to MAP proper arrays using MP
18
19=MP(1,B6:B8,D6:F6,H6:H8)=MP(2,B6:B8,D6:F6,H6:H8)=MP(3,B6:B8,D6:F6,H6:H8)
202020202020208910111
212021202120218910222
222022202220228910333
23
24calling MP as MAP array arguments
25=LET(y,B6:B8,m,D6:F6,d,H6:H8,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
2601-08-2001-09-2001-10-20
2702-08-2102-09-2102-10-21
2803-08-2203-09-2203-10-22
29
30check
31=DATE(B6:B8,D6:F6,H6:H8)
3201-08-2001-09-2001-10-20
3302-08-2102-09-2102-10-21
3403-08-2203-09-2203-10-22
35
36check
37ym=DATE(B38:B41,D38:F38,D41:F41)
382020891001-08-2002-09-2003-10-20
39202101-08-2102-09-2103-10-21
402022d01-08-2202-09-2203-10-22
41202312301-08-2302-09-2303-10-23
42
43=LET(y,B38:B41,m,D38:F38,d,D41:F41,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
4401-08-2002-09-2003-10-20
4501-08-2102-09-2103-10-21
4601-08-2202-09-2203-10-22
4701-08-2302-09-2303-10-23
48
MP 2
Cell Formulas
RangeFormula
B12,B43,H37,B31,B25,J19,F19,B19B12=FORMULATEXT(B13)
B13:D15B13=MAP(B6:B8,D6:F6,H6:H8,LAMBDA(y,m,d,DATE(y,m,d)))
B20:D22B20=MP(1,B6:B8,D6:F6,H6:H8)
F20:H22F20=MP(2,B6:B8,D6:F6,H6:H8)
J20:L22J20=MP(3,B6:B8,D6:F6,H6:H8)
B26:D28B26=LET(y,B6:B8,m,D6:F6,d,H6:H8,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
B32:D34B32=DATE(B6:B8,D6:F6,H6:H8)
H38:J41H38=DATE(B38:B41,D38:F38,D41:F41)
B44:D47B44=LET(y,B38:B41,m,D38:F38,d,D41:F41,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
Dynamic array formulas.
 
MAPS.xlsx
ABCDEFGHIJKLM
1
2If initial arrays are MAP unfriendly, MP will still expand them, but, like with DATE, will deliver flawed resuls
3
4ymd
520206781
6202189102
720223
820234
9
10=MP(1,B5:B8,D5:F6,H5:H8)=MP(2,B5:B8,D5:F6,H5:H8)=MP(3,B5:B8,D5:F6,H5:H8)
11202020202020678111
122021202120218910222
13202220222022#N/A#N/A#N/A333
14202320232023#N/A#N/A#N/A444
15
16=LET(y,B5:B8,m,D5:F6,d,H5:H8,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
1701-06-2001-07-2001-08-20
1802-08-2102-09-2102-10-21
19#N/A#N/A#N/A
20#N/A#N/A#N/A
21
22=DATE(B5:B8,D5:F6,H5:H8)
2301-06-2001-07-2001-08-20
2402-08-2102-09-2102-10-21
25#N/A#N/A#N/A
26#N/A#N/A#N/A
27
MP 3
Cell Formulas
RangeFormula
B10,B22,B16,J10,F10B10=FORMULATEXT(B11)
B11:D14B11=MP(1,B5:B8,D5:F6,H5:H8)
F11:H14F11=MP(2,B5:B8,D5:F6,H5:H8)
J11:L14J11=MP(3,B5:B8,D5:F6,H5:H8)
B17:D20B17=LET(y,B5:B8,m,D5:F6,d,H5:H8,MAP(MP(1,y,m,d),MP(2,y,m,d),MP(3,y,m,d),LAMBDA(a,b,c,DATE(a,b,c))))
B23:D26B23=DATE(B5:B8,D5:F6,H5:H8)
Dynamic array formulas.
 
MAPS.xlsx
ABCDEFGHIJKLMNOPQRS
1MP function. Concept
2
3 =LAMBDA(n,a,b,[c],[d], LET( w, MAX(ROWS(a), ROWS(b), IFERROR(ROWS(c), 0), IFERROR(ROWS(d), 0)), l, MAX(COLUMNS(a), COLUMNS(b), IFERROR(COLUMNS(c), 0), IFERROR(COLUMNS(d), 0)), p, LAMBDA(x, IFNA(EXPAND(x, w, l), x)), SWITCH(n, 1, p(a), 2, p(b), 3, p(c), 4, p(d)) ) )
4
5 <= w=max(arrays rows) , if c or d are omitted ROWS will return errors, if error => rows=0
6 <= l=max(arrays clms) , if c or d are omitted COLUMNS will return errors, if error => clms=0
7 <= embedded lambda p(x), EXPANDS any array x to an w x l array
8 <= returns expanded array according to its index number
9
10
11
12adding an extra argument if needed.
13
14 =LAMBDA(n, a, b, [c], [d], [e], LET( w, MAX(ROWS(a), ROWS(b), IFERROR(ROWS(c), 0), IFERROR(ROWS(d), 0), IFERROR(ROWS(e), 0)), l, MAX(COLUMNS(a), COLUMNS(b), IFERROR(COLUMNS(c), 0),IFERROR(COLUMNS(d), 0), IFERROR(COLUMNS(e), 0)), p, LAMBDA(x, IFNA(EXPAND(x, w, l), x)), SWITCH(n, 1, p(a), 2, p(b), 3, p(c), 4, p(d), 5, p(e)) ) )
15
16
17
18
19
20
21
22
23
MP 4
 
A perfect example to prove the benefits of MP, is a function posted by Irobbo314,
NTHDAYOFMONTH: "takes 4 arguments which can be single cell references or ranges. The function will return the nth day of the month for a given year, month, day of week, and nth day of month."
Excel Formula: 
=LAMBDA(year, month, day_index, nth_day,
    MAP(
        year,
        month,
        day_index,
        nth_day,
        LAMBDA(y, m, d, n, WORKDAY.INTL(DATE(y, m, 0), n, REPLACE("111111", d, 0, "0")))
    )
)
It's not about writing a better function, the function looks fine, the only thing we are going to change are MAP's arguments using MP
This will turn the new function into a function capable of dealing with MAP friendly arrays.
NTHWDY(y, m, w, n) (used shorter names for arguments, structure left unchanged)
y: years array
m: months array
w: weekday or day's name index (1 for Mon,....7 for Sun)
n: nth occurrence of "w" , referenced to the beginning of the month
Excel Formula: 
=LAMBDA(y, m, w, n,
    MAP(
        MP(1, y, m, w, n),
        MP(2, y, m, w, n),
        MP(3, y, m, w, n),
        MP(4, y, m, w, n),
        LAMBDA(y, m, d, n, WORKDAY.INTL(DATE(y, m, 0), n, REPLACE("111111", d, 0, "0")))
    )
)
MAPS.xlsx
ABCDEFGHIJKLMNOPQ
1
2NTHDAYOFMONTHNTHWDY
3
4 1. 2nd Wed of Sep 2022
5checking wdy
6=NTHDAYOFMONTH(2022,9,3,2)=NTHWDY(2022,9,3,2)
7#VALUE!14-09-22Wed
8
92. sequence of 5 Wed's starting with September, horizontally displayed
10
11=NTHDAYOFMONTH(2022,9,3,SEQUENCE(,5))=NTHWDY(2022,9,3,SEQUENCE(,5))
12#VALUE!07-09-2214-09-2221-09-2228-09-2205-10-22WedWedWedWedWed
13
143. 1st, 3rd 5th and 7th of wkdays Mon,Fri,Sun starting with Sep 2022
15
16=NTHDAYOFMONTH(2022,9,{1;5;7},{1,3,5,7})=NTHWDY(2022,9,{1;5;7},{1,3,5,7})
17#VALUE!05-09-2219-09-2203-10-2217-10-22MonMonMonMon
1802-09-2216-09-2230-09-2214-10-22FriFriFriFri
1904-09-2218-09-2202-10-2216-10-22SunSunSunSun
20
214. 4th Fri, of Sep, Oct, Nov, Dec, for the following 4 years
22
23=NTHDAYOFMONTH(SEQUENCE(5,,2022),{9,10,11,12},5,4)=NTHWDY(SEQUENCE(5,,2022),{9,10,11,12},5,4)
24#VALUE!23-09-2228-10-2225-11-2223-12-22FriFriFriFri
2522-09-2327-10-2324-11-2322-12-23FriFriFriFri
2627-09-2425-10-2422-11-2427-12-24FriFriFriFri
2726-09-2524-10-2528-11-2526-12-25FriFriFriFri
2825-09-2623-10-2627-11-2625-12-26FriFriFriFri
29
30
MP 5
Cell Formulas
RangeFormula
B6,B23,F23,B16,F16,B11,F11,F6B6=FORMULATEXT(B7)
B7B7=NTHDAYOFMONTH(2022,9,3,2)
F7F7=NTHWDY(2022,9,3,2)
L7L7=TEXT(F7,"ddd")
B12B12=NTHDAYOFMONTH(2022,9,3,SEQUENCE(,5))
F12:J12F12=NTHWDY(2022,9,3,SEQUENCE(,5))
L12:P12,L24:O28,L17:O19L12=TEXT(F12#,"ddd")
B17B17=NTHDAYOFMONTH(2022,9,{1;5;7},{1,3,5,7})
F17:I19F17=NTHWDY(2022,9,{1;5;7},{1,3,5,7})
B24B24=NTHDAYOFMONTH(SEQUENCE(5,,2022),{9,10,11,12},5,4)
F24:I28F24=NTHWDY(SEQUENCE(5,,2022),{9,10,11,12},5,4)
Dynamic array formulas.
 
MAPS.xlsx
ABCDEFGHIJKL
1Task: Year 2022, Only 30 days months, what weekdays have the 5 occurrence same month and not next one
2
3
430 days
5months1234567
6
7402-05-2203-05-2204-05-2205-05-2229-04-2230-04-2201-05-22
8604-07-2205-07-2229-06-2230-06-2201-07-2202-07-2203-07-22
9903-10-2204-10-2205-10-2229-09-2230-09-2201-10-2202-10-22
101105-12-2229-11-2230-11-2201-12-2202-12-2203-12-2204-12-22
11=NTHWDY(2022,B7:B10,D5:J5,5)
12
13
14=MONTH(D7#)
155555445
167766777
17101010991010
1812111112121212
19
20=--(D15#=B7:B10)
210000110
220011000
230001100
240110000
25
26
MP 6
Cell Formulas
RangeFormula
D7:J10D7=NTHWDY(2022,B7:B10,D5:J5,5)
D11D11=FORMULATEXT(D7)
D14,D20D14=FORMULATEXT(D15)
D15:J18D15=MONTH(D7#)
D21:J24D21=--(D15#=B7:B10)
Dynamic array formulas.
 
Now, after filling in all the tools and concepts, the real deal:
Introducing EMAP, a lambda helper function that works with up to 4 MAP friendly array arguments. (Can be extended to work with as many arguments as we want)
Same syntax as a regular MAP function (saves the bother of writing array arguments as MP(1,x,y..),MP(2,x,y,…)…).
EMAP(a,b,[c],[d],[fn]) Expand MAP lambda helper function. Calls MP
a,b,c,d: arrays
fn: helper function LAMBDA(...
The arguments are assigned dynamically, EMAP knows that always the last argument is a function, like in MAP
Excel Formula: 
=LAMBDA(a, b, [c], [d], [fn],
    IF(
        TYPE(c) = 128,
        MAP(MP(1, a, b), MP(2, a, b), LAMBDA(x, y, c(x, y))),
        IF(
            TYPE(d) = 128,
            MAP(
                MP(1, a, b, c),
                MP(2, a, b, c),
                MP(3, a, b, c),
                LAMBDA(x, y, z, d(x, y, z))
            ),
            MAP(
                MP(1, a, b, c, d),
                MP(2, a, b, c, d),
                MP(3, a, b, c, d),
                MP(4, a, b, c, d),
                LAMBDA(x, y, z, w, fn(x, y, z, w))
            )
        )
    )
)
MAPS.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1checking functionalitydynamic arguments: Observe that, like in regular MAP syntax,
2is no arguments gap btwn arrays arguments and fn
32 arrays
4ab=EMAP(B5:B8,D5:E5,LAMBDA(a,b,a+b))
511223
6234
7345
8456
9
103 arrays
11abc=EMAP(B12:B15,D12:E12,G12:G15,LAMBDA(a,b,c,a*b&c))
12112A1A2A
132B2B4B
143C3C6C
154D4D8D
16
17
184 arrays
19ab=EMAP(B20:B23,D20:G20,D23:D26,F23:F26,LAMBDA(a,b,c,d,(a+b)^c/d))
20123450.91.62.53.6
2124.278.3314.422.9
223cd31.364.8120205
23421031167213112362
24315
25420
26525
27
Sheet8
Cell Formulas
RangeFormula
J4,J19,J11J4=FORMULATEXT(J5)
J5:K8J5=EMAP(B5:B8,D5:E5,LAMBDA(a,b,a+b))
J12:K15J12=EMAP(B12:B15,D12:E12,G12:G15,LAMBDA(a,b,c,a*b&c))
J20:M23J20=EMAP(B20:B23,D20:G20,D23:D26,F23:F26,LAMBDA(a,b,c,d,(a+b)^c/d))
Dynamic array formulas.
 
Real deal challenges for real deal EMAP:
MAPS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2NameProd.CostU.PrcRegionReg.DiscUnt.SoldRevenueProfitlambda formulas using EMAP for PT alike reports in 2 sec
3Mel ScheidlerMajestic812South0.0512136.840.8
4Antione GrinnellQuad710East0.06109424=TOROW(SORT(UNIQUE(Tb[Prod.])))Sum of RevenueColumn Labels
5Mel ScheidlerCrested514North0.0411147.8492.84Sum RevenueCrestedMajesticQuadSunsetYanakiRow LabelsCrestedMajesticQuadSunsetYanakiGrand Total
6Rey McglothlinCrested514South0.051013383Antione Grinnell172.9019161.750Antione Grinnell172.919161.75425.65
7Theodore KrigerSunset913South0.05674.120.1Antione Scheidler213.080190122.2207.79Antione Scheidler213.08190122.2207.79733.07
8Theodore KrigerQuad710East0.0612112.828.8Freddie Otten78.961140146.640Freddie Otten78.96114146.64339.6
9Antione ScheidlerCrested514East0.0610131.681.6Houston Ybarbo031811475.66103.4Houston Ybarbo31811475.66103.4611.06
10Antione ScheidlerQuad710East0.06109424Jesus Pasquariello0273.72123.5335.010Jesus Pasquariello273.72123.5335.01732.23
11Freddie OttenCrested514East0.06678.9648.96Mel Scheidler295.68136.80248.3370.15Mel Scheidler295.68136.8248.3370.151050.93
12Rey McglothlinQuad710West0.0315145.540.5Rey Mcglothlin214.48264.84357.8052.8Rey Mcglothlin214.48264.84357.852.8889.92
13Mel ScheidlerYanaki611West0.0312128.0456.04Theodore Kriger135.8114294.6222.30Theodore Kriger135.8114294.6222.3766.7
14Mel ScheidlerSunset913East0.0610122.232.2=SORT(UNIQUE(Tb[Name]))Grand Total1110.91221.41270.91211.86734.145549.16
15Mel ScheidlerCrested514North0.0411147.8492.84N6:=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Revenue])))
16Freddie OttenSunset913East0.0612146.6438.64
17Theodore KrigerMajestic812South0.051011434Sum ProfitSum of ProfitColumn Labels
18Houston YbarboSunset913West0.03675.6621.66=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Profit])))Row LabelsCrestedMajesticQuadSunsetYanakiGrand Total
19Rey McglothlinYanaki611North0.04552.822.8107.905116.750Antione Grinnell107.95116.75175.65
20Houston YbarboYanaki611East0.0610103.443.4133.0805032.287.79Antione Scheidler133.085032.287.79303.07
21Theodore KrigerQuad710North0.0413124.833.848.9634038.640Freddie Otten48.963438.64121.6
22Jesus PasquarielloSunset913West0.0313163.9346.930943021.6643.4Houston Ybarbo943021.6643.4189.06
23Rey McglothlinCrested514West0.03681.4851.48081.7232.592.010Jesus Pasquariello81.7232.592.01206.23
24Freddie OttenMajestic812South0.051011434185.6840.8068.3160.15Mel Scheidler185.6840.868.3160.15454.93
25Houston YbarboMajestic812West0.03669.8421.84134.4880.8498.8022.8Rey Mcglothlin134.4880.8498.822.8336.92
26Antione ScheidlerSunset913East0.0610122.232.285.83477.660.30Theodore Kriger85.83477.660.3257.7
27Houston YbarboMajestic812East0.0612135.3639.36Grand Total695.9365.36339.9329.86314.142045.16
28Jesus PasquarielloMajestic812West0.03669.8421.84
29Houston YbarboMajestic812East0.0610112.832.8Sum Units SoldSum of Unt.SoldColumn Labels
30Jesus PasquarielloQuad710South0.0513123.532.5=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Unt.Sold])))Row LabelsCrestedMajesticQuadSunsetYanakiGrand Total
31Jesus PasquarielloSunset913East0.0614171.0845.081302050Antione Grinnell1320538
32Antione ScheidlerQuad710North0.04109626160201020Antione Scheidler1620102066
33Mel ScheidlerSunset913West0.0310126.136.16100120Freddie Otten6101228
34Antione ScheidlerCrested514West0.03681.4851.4802812610Houston Ybarbo281261056
35Theodore KrigerCrested514West0.0310135.885.802413270Jesus Pasquariello24132764
36Theodore KrigerQuad710South0.0565715221202035Mel Scheidler2212203589
37Rey McglothlinMajestic812South0.05668.420.416233705Rey Mcglothlin162337581
38Mel ScheidlerYanaki611West0.0313138.7160.71101031180Theodore Kriger1010311869
39Antione GrinnellCrested514South0.0513172.9107.9Grand Total831071339870491
40Jesus PasquarielloMajestic812South0.05779.823.8
41Rey McglothlinMajestic812West0.0311128.0440.04
42Houston YbarboQuad710South0.051211430Average ofAverage of RevenueColumn Labels
43Rey McglothlinQuad710West0.0311106.729.7RevenueRow LabelsCrestedMajesticQuadSunsetYanakiGrand Total
44Mel ScheidlerYanaki611East0.0610103.443.4172.995.561.75Antione Grinnell172.995.561.75106.4125
45Antione GrinnellQuad710West0.03109727106.5495122.2103.895Antione Scheidler106.5495122.2103.895104.7242857
46Rey McglothlinQuad710North0.0411105.628.678.96114146.64Freddie Otten78.96114146.64113.2
47Theodore KrigerSunset913South0.0512148.240.210611475.66103.4Houston Ybarbo10611475.66103.4101.8433333
48Antione ScheidlerYanaki611South0.05994.0540.0591.24123.5167.505Jesus Pasquariello91.24123.5167.505122.0383333
49Jesus PasquarielloMajestic812East0.0611124.0836.08147.84136.8124.15123.3833Mel Scheidler147.84136.8124.15123.3833333131.36625
50Antione ScheidlerYanaki611East0.0611113.7447.74107.2488.28119.266752.8Rey Mcglothlin107.2488.28119.266666752.898.88
51Rey McglothlinMajestic812South0.05668.420.4135.811498.2111.15Theodore Kriger135.811498.2111.15109.5285714
52Antione GrinnellSunset913South0.05561.7516.75Grand Total123.4333333101.78105.9083333121.186104.8771429110.9832
53
54N44:=EMAP(M6#,N5#,LAMBDA(x,y,LET(z,(x=Tb[Name])*(y=Tb[Prod.])*Tb[Revenue],IFERROR(AVERAGE(IF(z,z)),""))))
55
EMAP 1
Cell Formulas
RangeFormula
N4N4=FORMULATEXT(N5)
N5:R5N5=TOROW(SORT(UNIQUE(Tb[Prod.])))
M6:M13M6=SORT(UNIQUE(Tb[Name]))
N6:R13N6=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Revenue])))
M14M14=FORMULATEXT(M6)
M15M15=FORMULATEXT(N6)
M18,M30M18=FORMULATEXT(N19)
N19:R26N19=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Profit])))
N31:R38N31=EMAP(M6#,N5#,LAMBDA(x,y,SUM((x=Tb[Name])*(y=Tb[Prod.])*Tb[Unt.Sold])))
N44:R51N44=EMAP(M6#,N5#,LAMBDA(x,y,LET(z,(x=Tb[Name])*(y=Tb[Prod.])*Tb[Revenue],IFERROR(AVERAGE(IF(z,z)),""))))
M54M54=FORMULATEXT(N44)
Dynamic array formulas.
 
MAPS.xlsx
ABCDEFGHIJKLMNOPQR
1
2Using third array argument in EMAP as filterRegion
3FilterRegionEast
4
5Sum RevenueCrestedMajesticQuadSunsetYanakiEastSum of RevenueColumn Labels
6Antione Grinnell009400NorthRow LabelsCrestedMajesticQuadSunsetYanakiGrand Total
7Antione Scheidler131.6094122.2113.74SouthAntione Grinnell9494
8Freddie Otten78.9600146.640WestAntione Scheidler131.694122.2113.74461.54
9Houston Ybarbo0248.1600103.4Freddie Otten78.96146.64225.6
10Jesus Pasquariello0124.080171.080Houston Ybarbo248.16103.4351.56
11Mel Scheidler000122.2103.4Jesus Pasquariello124.08171.08295.16
12Rey Mcglothlin00000Mel Scheidler122.2103.4225.6
13Theodore Kriger00112.800Theodore Kriger112.8112.8
14Grand Total210.56372.24300.8562.12320.541766.26
15
1600000
17009600
1800000=EMAP(B6#,C5#,I5,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
1900000=EMAP(B6#,C5#,I6,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
2000000=EMAP(B6#,C5#,I7,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
21295.680000=EMAP(B6#,C5#,I8,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
2200105.6052.8
2300124.800
24
25
26172.90061.750
27000094.05
280114000
290011400
30079.8123.500
310136.8000
32133136.8000
33011457222.30
34
35
36009700
3781.480000
3800000
39069.84075.660
40069.840163.930
41000126.1266.75
4281.48128.04252.200
43135.80000
44
EMAP 2
Cell Formulas
RangeFormula
C5:G5C5=TOROW(SORT(UNIQUE(Tb[Prod.])))
I5:I8I5=SORT(UNIQUE(Tb[Region]))
B6:B13B6=SORT(UNIQUE(Tb[Name]))
C6:G13C6=EMAP(B6#,C5#,I5,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
C16:G23C16=EMAP(B6#,C5#,I6,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
J18J18=FORMULATEXT(C6)
J19J19=FORMULATEXT(C16)
J20J20=FORMULATEXT(C26)
J21J21=FORMULATEXT(C36)
C26:G33C26=EMAP(B6#,C5#,I7,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
C36:G43C36=EMAP(B6#,C5#,I8,LAMBDA(x,y,z,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue])))
Dynamic array formulas.
 
We did filtering, now let's do grouping, with formulas using only EMAP, nothing else. I hope one day Excel will give us MAP with EMAP's functionality built in.
We only have to iterate the previous filter formula for all values in region array, hstacking the iterations. Adding totals is also possible.
MAPS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2Sum RevenueCrestedMajesticQuadSunsetYanakiEast
3Antione Grinnell 94North
4Antione Scheidler131.694122.2113.74South
5Freddie Otten78.96146.64West
6Houston Ybarbo248.16103.4
7Jesus Pasquariello124.08171.08
8Mel Scheidler122.2103.4
9Rey Mcglothlin
10Theodore Kriger112.8
11=EMAP(B3#,C2#,I2,LAMBDA(x,y,z,LET(s,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue]),IF(s,s,""))))
12
13
14=IFNA(DROP(REDUCE("",I2#,LAMBDA(v,f,HSTACK(v,VSTACK(f,C2#,EMAP(B3#,C2#,f,LAMBDA(x,y,z,LET(s,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue]),IF(s,s,"")))))))),,1),"")
15EastNorthSouthWest
16CrestedMajesticQuadSunsetYanakiCrestedMajesticQuadSunsetYanakiCrestedMajesticQuadSunsetYanakiCrestedMajesticQuadSunsetYanaki
1794172.961.7597
18131.694122.2113.749694.0581.48
1978.96146.64114
20248.16103.411469.8475.66
21124.08171.0879.8123.569.84163.93
22122.2103.4295.68136.8126.1266.75
23105.652.8133136.881.48128.04252.2
24112.8124.811457222.3135.8
25check
26Pivot Table
27Sum of RevenueColumn Labels
28EastEast TotalNorthNorth TotalSouthSouth TotalWestWest TotalGrand Total
29Row LabelsCrestedMajesticQuadSunsetYanakiCrestedQuadYanakiCrestedMajesticQuadSunsetYanakiCrestedMajesticQuadSunsetYanaki
30Antione Grinnell9494172.961.75234.659797425.65
31Antione Scheidler131.694122.2113.74461.54969694.0594.0581.4881.48733.07
32Freddie Otten78.96146.64225.6114114339.6
33Houston Ybarbo248.16103.4351.5611411469.8475.66145.5611.06
34Jesus Pasquariello124.08171.08295.1679.8123.5203.369.84163.93233.77732.23
35Mel Scheidler122.2103.4225.6295.68295.68136.8136.8126.1266.75392.851050.93
36Rey Mcglothlin105.652.8158.4133136.8269.881.48128.04252.2461.72889.92
37Theodore Kriger112.8112.8124.8124.811457222.3393.3135.8135.8766.7
38Grand Total210.56372.24300.8562.12320.541766.3295.68326.452.8674.88305.9581.4294.5284.0594.051559.9298.76267.72349.2365.69266.751548.125549.16
39
40
EMAP 3
Cell Formulas
RangeFormula
C2:G2C2=TOROW(SORT(UNIQUE(Tb[Prod.])))
I2:I5I2=SORT(UNIQUE(Tb[Region]))
B3:B10B3=SORT(UNIQUE(Tb[Name]))
C3:G10C3=EMAP(B3#,C2#,I2,LAMBDA(x,y,z,LET(s,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue]),IF(s,s,""))))
C11C11=FORMULATEXT(C3)
C14C14=FORMULATEXT(C15)
C15:V24C15=IFNA(DROP(REDUCE("",I2#,LAMBDA(v,f,HSTACK(v,VSTACK(f,C2#,EMAP(B3#,C2#,f,LAMBDA(x,y,z,LET(s,SUM((x=Tb[Name])*(y=Tb[Prod.])*(z=Tb[Region])*Tb[Revenue]),IF(s,s,"")))))))),,1),"")
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,526
Messages
6,172,833
Members
452,483
Latest member
Johnstone

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top