Ravindra BagaleCourses & study guides मराठी Track your progress

9. Dynamic Arrays

Practice and Answer Key: New Dynamic Array and LAMBDA Functions

By Last updated:

Goal of this practice: Clean messy notes with REGEX, summarise with GROUPBY and PIVOTBY, and build a named LAMBDA with an optional argument.

Suppose Ravina gets 8 fictional orders from three cities. The order ID is hidden inside a free-text note, sometimes in lower case. She needs a clean ID column, a city summary, a city by platform table, a late flag with a store-specific limit, and a running total. Data is in A1:E9. All data is fictional practice data.

A: Note B: City C: Platform D: Amount (₹) E: Mins
Rider note: BLK-2001 handed over, Nagpur Nagpur Blinkit 180 12
late! amn-2002 reached in 17 min Kolhapur Amazon Now 95 17
BLK-2003 ok Nagpur Blinkit 420 9
customer called twice, BLK-2004 Solapur Blinkit 60 21
AMN-2005 paid Rs. 310 Nagpur Amazon Now 310 14
amn-2006 gate closed, waited Kolhapur Amazon Now 150 16
BLK-2007 ok Solapur Blinkit 275 11
BLK-2008 left with guard Kolhapur Blinkit 130 13

Exercise (one under the other):

  1. In F2, extract the order ID from the note, ignoring case, and show it in capitals. Fill down.
  2. In H2, show total Amount per City with GROUPBY.
  3. In H7, show City down the side and Platform across the top with PIVOTBY.
  4. Save a named function LATEFLAG(mins,[limit]) (default 15) and use it on E2:E9 with the default limit, then with 12.
  5. Count how many orders are Late with each limit.
  6. In G2, spill the running total of Amount with SCAN.
  7. With MAP, give 10% off to orders over 15 minutes and find the new total.

Expected result (check your numbers):

Step What you should see
F2:F9 order IDs BLK-2001, AMN-2002, BLK-2003, BLK-2004, AMN-2005, AMN-2006, BLK-2007, BLK-2008
GROUPBY Kolhapur 375, Nagpur 910, Solapur 335, Total 1620
PIVOTBY Kolhapur: AN 245, BLK 130, Total 375; Nagpur: AN 310, BLK 600, Total 910; Solapur: AN —, BLK 335, Total 335; Total: AN 555, BLK 1065, Total 1620
LATEFLAG default (15) On time, Late, On time, Late, On time, Late, On time, On time
LATEFLAG limit 12 On time, Late, On time, Late, Late, Late, On time, Late
Late count 3 with limit 15, 5 with limit 12
SCAN running total 180, 275, 695, 755, 1065, 1215, 1490, 1620
MAP total after coupons 1589.5
Answer key (open after you try) 
F2:  =UPPER(REGEXEXTRACT(A2,"(BLK|AMN)-\d{4}",0,1))      (fill down to F9)
H2:  =GROUPBY(B2:B9,D2:D9,SUM)
H7:  =PIVOTBY(B2:B9,C2:C9,D2:D9,SUM)
Name Manager › New › LATEFLAG › Refers to:
     =LAMBDA(mins,[limit],IF(mins>IF(ISOMITTED(limit),15,limit),"Late","On time"))
Late flags:   =LATEFLAG(E2:E9)        =LATEFLAG(E2:E9,12)
Late counts:  =SUM(--(LATEFLAG(E2:E9)="Late"))     =SUM(--(LATEFLAG(E2:E9,12)="Late"))
G2:  =SCAN(0,D2:D9,LAMBDA(acc,v,acc+v))
MAP: =SUM(MAP(D2:D9,E2:E9,LAMBDA(a,m,IF(m>15,a*0.9,a))))

Common mistakes:

What you see Why Fix
amn-2002 not found REGEXEXTRACT is case-sensitive by default Add 0,1 as return_mode and case_sensitivity
#SPILL! from GROUPBY or PIVOTBY Other cells are in the way (for example the PIVOTBY result runs into the GROUPBY result) Leave enough empty rows between the two results, or put them on separate sheets
#CALC! in the cell where you tested LAMBDA A LAMBDA typed in a cell without values after it Call it with test values, or save it in Name Manager
LATEFLAG(E2:E9,12) gives #VALUE! The name was saved without the optional [limit] Edit the name in Name Manager and add [limit]
#NAME? everywhere The workbook is open in a version without these functions Use Excel for Microsoft 365

Ravindra Bagale's rule: first make the data clean, then summarise it, and only then automate it with your own functions.