Spreadsheets and Stuff
Newsletter about all things Office from Avancer Learning Inc. Issue 1. January 2025
The First Newsletter!
Greetings from Avancer Learning Inc! I hope you like this first attempt at what hopefully will become a regular feature. I promise to do my best to keep it relevant, and ensure you get something to learn with each issue. Each issue I will cover just one tip from Microsoft Office that will hopefully be of use to you
The Magic of Dynamic Arrays in Office 365 – The SEQUENCE function
Through the 365 platform, Excel has constantly been updated with new features. Dynamic Arrays bring pack a lot of power and here we examine the simple yet powerful SEQUENCE function.
=SEQUENCE(rows,[columns],[start],[step])
Except the ‘rows’ argument, all the rest are optional with a default value of 1 for each of them if you leave them out.
If you haven’t quite used Dynamic Array formulas before, try this one out by typing into a single cell on a blank spreadsheet.
=SEQUENCE(10)
This will generate a sequence of 10 numbers in one column and the results will ‘Spill’ into cells one below the other. This in itself is no big deal.
The real advantage is the ability to nest within other new functions or even legacy functions. Here below are some sample formulas:
Generating Interest Payment calculations =IPMT(0.004,SEQUENCE(60),60,50000)
This will generate 60 values in one column representing Interest payments from period 1 to period 60 of an equated payment plan at an interest rate of 0.4% per period (presume monthly) for a principal borrowing of 50,000. Of course, the values presented here are for purposes of simplicity, and all of them can be replaced by cell references instead of hard numbers.
Generating Reporting Dates / End of Month =EOMONTH(TODAY(),SEQUENCE(6,1,0,1))
This will generate the month-end dates for 6 months including the current month. Keep in mind that the output of these dynamic array formulas are not formatted in a specific way so the cell range will have to be formatted with a date format as required.
Let your creative computing begin. You will doubtless develop your own creative ways of putting it to use in your work once you start using it! Enjoy!
STAY IN TOUCH!
Would you like to receive this Newsletter and other emails from Avancer? At Avancer we don’t flood your email with an email every day or every week. On an average you would perhaps receive one newsletter every month and one or two emails on upcoming courses.
Links you might be interested in:
The YouTube channel: https://youtube.com/avancerinc
Online calendar: https://avancerinc.com/online.html
What do participants say about Avancer – Google Reviews
Read our Google reviews: https://tinyurl.com/53b83ch9
YouTube video
Check out these 5 awesome Windows shortcuts that can speed up your work!
https://www.youtube.com/watch?v=cGyQ8kLbkq4
Copyright © 2025 Avancer Learning Inc, All rights reserved.