Using Dynamic Range

I’ve been working on this project that enabled me to use quite a few interesting functions and approaches: hyperlinks, EDATE, Agent Mode. And now dynamic range.

Dynamic ranges are ranges that grow and shrink depending on the situation.

Here are some visual examples of what I mean:

Pay attention to the section outlined in red. This file calculates the annual balance to an investment or savings account after monthly withdrawals. This is basically how long does the money lasts upon retirement. The image is showing an example of starting with savings/investment of $1.5 million.

In image 2, the only thing changed was the initial balance, going from $1.5 million to $500K. Noticed how the table is much smaller. Through a combination of formulas and conditional formatting, the table automatically adjusted size.

Setting up the dynamic ranges

Image 3 is the workhorse of the file and feeds into the file shown in Image 1 and 2.

For this table shown in Image 3, the key driver of the number of rows to use for a range is cell M3. The formula in that cell is:

=MAX(A:A)

Column A runs up the count until the balance becomes zero and at that point stops counting. Cell M3 shows the row number where the balance becomes zero. The image below shows that situation.

Now, using the number in cell M3, I’m going to create some dynamic ranges for date, age and balance.

Age: =OFFSET($B$4,0,0,$M$3+1,1)

Date: =OFFSET($C$4,0,0,$M$3+1,1)

Balance: =OFFSET($K$4,0,0,$M$3+1,1)

Offset formula contains 4 parts: 1) the starting point of the range; 2) the row offset; 3) the column offset; 4) how many rows; 5) how many columns.

So for age, the column for age is the B column and it starts in cell B4. Since I want to start from this cell, my row offset will be zero. If I had wanted to have the range start from B5, I would use 1 instead of zero as my row offset.

The column offset is zero since I do not want to move from column B.

The 4th item in the formula is how many rows I want the range to contain. For age, I want it to start from B4 and run all the way through B103 (or 99+1 rows).

And for the fifth item, I only want 1 column in the range.

To do these offset formulas, it does take a bit of playing around to get what you want, but once you got it, it wants beautifully.

Final important thing to add, you create these dynamic ranges as a named range. You can see in image 5, I have various named ranges, especially for age, date and balance.

Date spill

Let’s look at what happens if you do =Date or =Age or =Balance:

The two areas to look at in Image 6 is the yellowish highlight in the formula bar above the spreadsheet and the column outlined in red off to the lower right.

The formula bar shows:

=Date

The dates in the dynamic range spills down. I think the variable Date is a dynamic array. You could do the same with =Age and =Balance.

Setting up table in calculator (columns K-M)

Now, the table itself (under columns K through M) shows the dates in annual terms but =Date spilled out monthly dates. To keep the table small for the end user, I opted to show the balance on an annual basis rather than monthly.

I had to do some fancy work to just show the balance on annual basis rather than show every single month, which is the normal way of showing these calculations. The fancy work utilizes the FILTER dynamic array formula to pull out only the month matching the inputted retirement month in cell C11, so in this example pull out the balance for every May.

The function FILTER syntax is FILTER(the range, the criteria, response if not found)

So, let’s just show the formula I created in cell K17 to do this trick:

=FILTER(IF(MONTH(Date)=MONTH($C$11),1,0)*Date,IF(MONTH(Date)=MONTH($C$11),1,0)*Date>0,””)

Okay, this is scary looking but let’s try to break it down.

Remember, this FILTER formula is a dynamic array formula so FILTER can handle arrays in addition to a single number.

The range that the FILTER works upon is IF(MONTH(Date)=MONTH($C$11),1,0)*Date. Date is an array holding a series of monthly dates (5/1/2035, 6/1/2035, 7/1/2035,…). If the month of that date in the array is May, then assign 1; otherwise, assign zero. Multiply that array of 1s and zeros (1,0,0,…) with Date and you will get an array of May dates and zeros (5/1/2035,0,0,….5/1/2036,0,0…). That is the range to apply the filter.

The criteria is IF(MONTH(Date)=MONTH($C$11),1,0)*Date>0, or you are filtering the range for non-zeros.

The last one “” is for when you can’t find any May dates in the range. That is not the case in this example.

You have to work with arrays in formulas quite a bit to get a feel for it. I would just persist in trying to use arrays because they can help you solve really unusual problems.

FILTER formulas for age and balance

I’m just going to put down the formulas but won’t explain in detail how they work. They work very similarly to how the filter worked for Date.

Age =ROUND(FILTER(Age,IF(MONTH(Date)=MONTH($C$11),1,0)*Date>0,””),0)

Balance =FILTER(Balance,IF(MONTH(Date)=MONTH($C$11),1,0)*Date>0,””)

Both formulas appear simpler than the one composed for filtering dates since we are filtering specifically for a certain month. The filtering of age also includes a function for rounding the age.

Conditional formatting

I did set up some conditional formatting to show the table. Originally, the background and text are grey, but if there is a date, age and balance, the cell’s background turns white and the font turns black.

The other piece of this is the date format. Usually dates appear as mm/dd/yyyy (US format) but I can set the format as mm/yyyy or yyyy through the custom cell format. Right click the cell and go through Format Cells and check if you can find an alternative option. Or create a custom format through Format Cells. Usually you can find alternatives under the Format Cells – Custom.

I’m not going to show how to do the date format because I’m sure there are other blogs that will explain this much clearer than I would. It’s late at night, or rather morning (1 am in the morning) and I’m eager to get in bed.

I’m going to stop here