16. Practice Exercises with Answer Hints

16.8 Module 9: Dynamic Arrays

By Ravindra Bagale · Last updated: 25 September 2026

1. List unique cities from the Orders Table. Hint: =SORT(UNIQUE(tblOrders[City])) (Microsoft 365 / Excel 2021+).
2. Show all Solapur orders above ₹500. Hint: =FILTER(tblOrders,(tblOrders[City]="Solapur")*(tblOrders[Amount]>500),"None").
3. Top 5 orders by Amount. Hint: =TAKE(SORT(tblOrders,MATCH("Amount",tblOrders[#Headers],0),-1),5) (Microsoft 365 / Excel 2024).
4. City-wise totals next to the UNIQUE list using the spill reference. Hint: =SUMIFS(tblOrders[Amount],tblOrders[City],E2#).
5. Generate the dates 01-10-2026 to 31-10-2026. Hint: =SEQUENCE(31,1,DATE(2026,10,1)) and format as dd-mm-yyyy.
6. Rewrite a long formula with LET. Hint: =LET(s,SUM(...),n,COUNT(...),IF(n=0,0,s/n)).