## Office 365 keeps bringing us new stuff

Last week, I wrote my first blog post in a few months. I wrote about Watching my Weight with SPC (Statistical Process Control).

In the first comment, Derek asked for a box plot, so I quickly recompiled my data and built one.

Several new features of Office 365 made it very easy to mush my data around, and I realized that this is a good example to show them in action. I used Dynamic Arrays, the `XLOOKUP`

function, and the new `LET`

function.

## Dynamic Arrays

Joe McDaid of Microsoft’s Excel team wrote about Dynamic Arrays in Dynamic Arrays and New Functions in Excel! These formulas break the old paradigm of one formula per cell. In classic Excel, you needed a formula for every value you calculated. Sure, we had array formulas, but you had to include all output cells together and enter an array formula in all of the cells at once. If you didn’t use the right number of formulas, or if your model expanded, you had to rebuild the formula. Ugh! Plus array formulas, amirite?

The new Dynamic Arrays allow you to enter a formula into one cell. Excel figures out how many cells you need, and “spills” the formula into the required range of cells. One formula, multiple cells. And with a retooling of Excel’s underlying grid, any formula can become a Dynamic Array.

In Preview of Dynamic Arrays in Excel, Joe tells us that Dynamic Arrays have rolled out to Office 365 subscribers, with users in the Semi-Annual (Targeted) channel getting them starting last month. I presume that users in the Semi-Annual channel getting them soon as well.

I used a few Dynamic Array Formulas in my example.

## XLOOKUP

For years there has been a debate about which is better, `VLOOKUP`

or `INDEX/MATCH`

. I don’t know why there was a debate, because `INDEX/MATCH`

is clearly the superior choice. Like Zoolander, `VLOOKUP`

can’t turn left, and there were many things that could go wrong with your lookups. `INDEX/MATCH`

has its own problems as well, mostly because of the default `Match_Type`

setting which could lead to bad results.

In Announcing `XLOOKUP`

, again by Joe McDaid, we learned of this new lookup function that eliminates the perils and shortcomings of `VLOOKUP`

. It’s simpler to use and more flexible, and best of all, it begins with `XL`

. You can read documentation in `XLOOKUP`

Function. `XLOOKUP`

is on about the same release schedule as Dynamic Arrays.

## LET

According to Announcing `LET`

, the new function allows you to assign a name to a value or intermediate calculation at the beginning of the formula, then use this named argument in the main calculation. The `LET`

function improves performance by computing an intermediate calculation just once and allowing reuse of the computed value, and it helps readability of your formulas by allowing you to use descriptive labels for these intermediate calculations.

`LET`

is only available to Office Insiders at this writing, so you’ll have to be patient.

## Recompiling the Weight Data

### The Data

In my previous post, I examined fluctuations in my body weight between last September and this April. The data is in a two-column Table. I will construct a table in the worksheet, starting in cell D2, which has month and year as column headers and day numbers in the first column of each row. This will produce a grid of mostly weights with some missing values, and it feeds directly into Peltier Tech Charts for Excel, the VBA add-in I use to make box plots.

### Set up the Grid Headers with Dynamic Arrays

I need to show the months from September through April across the top. I will use the new `SEQUENCE`

function to make a list. In cell E2, I enter the formula

=SEQUENCE(1,8,9,1)

Which tells Excel to make a sequence with 1 row, 8 columns, start the sequence at 9, and increment by 1. And you see the Dynamic Array in E2:L2 filled with this sequence. If the active cell is within this range, the entire Dynamic Array range is highlighted as shown.

But Jon, you may say, you need the months to go up to 12, then start again at 1. And the answer is, no I don’t. A few rows below the first Dynamic Array, I have written a temporary formula to check the dates:

=DATE(2019,E2#,1)

Which tells Excel to give me a date using 2019 as the year, `E2#`

as the month, and 1 as the day. The hash or pound sign appended to `E2#`

tells Excel to use the Entire Dynamic Array that is anchored in cell E2. So I’ve entered this formula in cell E6 only, but it spills into the range E6:L6 to accommodate `E2#`

.

And we see that if I define a date using the year 2019 and the month 13, it uses 12 months to increment the year to 2020, and uses the leftover month, so I get January 2020. Well, you probably new that, but it’s a nice little trick.

In row 9 I repeat the previous formula, but with 31 days. Excel does the same thing with excess days, and converts September 31 to October 1, etc.

So Dynamic Arrays, pretty cool, right?

Now to get the dates I want, I’ll wrapt `SEQUENCE`

in the `DATE`

function:

=DATE(2019,SEQUENCE(1,8,9,1),1)

and Excel gives me the first of each month in the study.

Now let’s fill in the column of days. I’ll use another `SEQUENCE`

function in cell D3, an easier one this time:

=SEQUENCE(31,1,1,1)

Which is a sequence of 31 rows and 1 column, starting with 1 and incrementing by 1.

### Populate the Grid with Dynamic Array Dates

In cell E3, I entered a formula to calculate the dates for my lookups.

=DATE(YEAR(E2#),MONTH(E2#),D3#)

Meaning, give me the data using the year and month from the date `E2#`

and the day from `D3#`

. This new Dynamic Array fills up the whole rectangular range defined by the two Dynamic Arrays it references. Note the extra dates in the September, November, February, and April columns.

### Make Dynamic Arrays Perform the XLOOKUP

The dates are fine (except for those extras, which we’ll take care of soon). So let’s wrap the date in `XLOOKUP`

.

=XLOOKUP(DATE(YEAR(E2#),MONTH(E2#),D3#), Table6[Date], Table6[Weight], "")

I’ve put each argument onto its own line for clarity. Basically, I’m telling Excel to look for the date we’ve calculated, look in the Date column of Table 6, and if you find it, return the value from the Weight column of Table 6. And if you don’t find it, give me a cell that looks blank with “”.

And here are the weights, including some duplications: note that the 10/1/2019 weight of 167 appears at the top of the October column but also at the bottom of the September column.

### Remove Duplicate Weights

So how do I eliminate those duplicates? Even though Excel computes `DATE(2019,9,31)`

using month 9, the result is month 10. So I’ll enter a formula that checks whether the month calculated in the lookup cell matches the month in the header cell: if so, perform the `XLOOKUP`

, but if not, return “”.

=IF(MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#))=MONTH(E2#), XLOOKUP(DATE(YEAR(E2#),MONTH(E2#),D3#), Table7[Date],Table7[Weight],""),"")

Clever trick to skip those duplicates, Jon, but that’s an unwieldy formula. That big chunk `MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#)`

appears twice, and we’ve all written monstrosities which has more than one chunk like this repeated more than twice.

### LET’s Try Out LET

So I’m going to take the new function `LET`

out for a spin. I’ll create an argument called `TheDate`

, and it will stand for `MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#`

). Then in the calculation, I’ll use `TheDate`

where the big chunk was before.

=LET(TheDate,DATE(YEAR(E2#),MONTH(E2#),D3#), IF(MONTH(TheDate)=MONTH(E2#), XLOOKUP(TheDate,Table8[Date],Table8[Weight],""),""))

This formula is only a few characters shorter than the original, but it’s certainly a lot easier to read. And if I had to change something in the definition of `TheDate`

, I would only have to change it in one place, and not worry that I forgot to change it somewhere else.

Now all I have to do is select the data and insert my box plot.

## Power Query

Of course, Excel often gives you many ways to accomplish the same objective. You can use Power Query to manipulate this data in the same way, as I show in a follow-up article, Use Power Query to Manipulate Your Data.

derek says

Still not rolled out in my employers’ Office 365, grrr.

Steve Bayliss says

As always Jon, an excellent post. You have a great knack of explaining concepts and the logical way you explain your processes is very easy to follow. I met you many years ago at a forum in Sydney and was a fan many years before that. Probably no one in the world more knowledgeable on excel charting techniques than yourself, keep up the great work, it is appreciated. Well done mate.