9.5 SEQUENCE and RANDARRAY
=SEQUENCE(rows, [columns], [start], [step])– a list of numbers.=RANDARRAY([rows], [columns], [min], [max], [integer])– random numbers (volatile).
| Need | Formula |
|---|---|
| 1 to 10 | =SEQUENCE(10) |
| All dates of Ganeshotsav 2026 period | =SEQUENCE(12, 1, DATE(2026,9,14), 1) (format dd-mm-yyyy) |
| First day of each month, FY 2026-27 | =EDATE(DATE(2026,4,1), SEQUENCE(12,1,0)) |
| Row numbers for a filtered list | =SEQUENCE(ROWS(FILTER(tblMini, tblMini[City]="Pune"))) |
| 10 random delivery times 5–25 (practice data) | =RANDARRAY(10, 1, 5, 25, TRUE) |
Worked example – practice data generator. Ruhi creates 100 fictional orders for a test: Qty =RANDARRAY(100,1,1,5,TRUE), City =INDEX({"Pune";"Nashik";"Nagpur";"Kolhapur";"Solapur";"Sambhaji Nagar"}, RANDARRAY(100,1,1,6,TRUE)). She then Copy › Paste Values so the numbers stop changing.
Ravindra Bagale's Tip
RANDARRAY pratyek recalculation la badalto – khup students practice data banvtat aani dusrya divshi sagle aakde badalleli distat. Random data banavlyavar lagech Paste Values kara. SEQUENCE ne date list banavtana cells la date format lavayla visru naka.
Practice task
Create a calendar of all dates in November 2026 with SEQUENCE and mark weekends. Generate 50 random fictional orders and freeze them with Paste Values.