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

16. Practice Exercises with Answer Hints

16.8 Module 9: Dynamic Arrays

By Last updated:

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

Ravindra Bagale's Tip

Many students panic when they see the #SPILL! error. It only means there's no empty space for the answer to spread into (spill) – clear the cells below. And remember, these functions work only in Microsoft 365 / Excel 2021+.

Loading practice tasks...

  1. Distinct groups (Easy): How many different values in the "Carrier" column?
  2. List groups (Easy): Unique list of the "Carrier" column.
  3. Filter names (Easy): Names in the "Name" column where "Carrier" is "Royal Mail".
  4. Sum of filtered (Medium): Sum of the "Parcels" column where "Carrier" is "DPD", using FILTER.
  5. Count filtered (Medium): Rows where "Parcels" is above the average of the "Parcels" column.
  6. Sorted values (Medium): the "Parcels" column sorted largest first.
  7. Top value via SORT (Medium): The largest the "Cost (£)" column value using SORT and INDEX.
  8. Second largest (Hard): Second largest the "Parcels" column using SORT and INDEX.
  9. Numbers 1 to 5 (Hard): A spill of numbers 1 to 5.
  10. Two-condition filter (Hard): Sum of the "Parcels" column where "Carrier" is "Royal Mail" or "DPD".
  11. Names joined (Hard): Names where "Carrier" is "Royal Mail", joined with ", ".

See all practice tasks in the Task Hub →

Worked example — module 9 dynamic arrays

Why

Aarti Kulkarni in Pune and Yuki Tanaka abroad both need a clear, repeatable way to use module 9 dynamic arrays on a Maple Bakery Co sheet without breaking totals.

What

Module 9 Dynamic Arrays is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for module 9 dynamic arrays.
  2. Enter a small made-up value for Aarti Kulkarni using $ where money appears.
  3. Add a second value for Yuki Tanaka using € so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for Maple Bakery Co's sample row.
Meaning: Once module 9 dynamic arrays is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Module 9 Dynamic Arrays?
Card 1: What is Module 9 Dynamic Arrays? · Download PNG
Revision card 2: Tiny example — Module 9 Dynamic Arrays
Card 2: Tiny example — Module 9 Dynamic Arrays · Download PNG
Revision card 3: Checklist — Module 9 Dynamic Arrays
Card 3: Checklist — Module 9 Dynamic Arrays · Download PNG