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 changes on every recalculation – many students create practice data and the next day find all the numbers have changed. Right after creating random data, do Paste Values. When creating a date list with SEQUENCE, don't forget to apply a date format to the cells.
Ravindra Bagale's Tip – मराठी
RANDARRAY प्रत्येक recalculation ला बदलतो – बरेच students practice data बनवतात आणि दुसऱ्या दिवशी सगळे आकडे बदललेले दिसतात. Random data बनवल्यावर लगेच Paste Values करा. SEQUENCE ने date list बनवताना cells ला date format लावायला विसरू नका.
Ravindra Bagale's Tip – हिंदी
RANDARRAY हर recalculation पर बदल जाता है – बहुत से students practice data बनाते हैं और अगले दिन सारे आँकड़े बदले हुए मिलते हैं. Random data बनाते ही Paste Values करो. SEQUENCE से date list बनाते समय cells पर date format लगाना मत भूलना.
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.