A balance between simplicity and automation often needs to be struck in the construction of a spreadsheet. The usual advice is to keep Excel formulae as simple as possible, on the basis that the more complex the formula, the greater the likelihood of introducing an error, and the less easy it is for the user to understand how the formula is intended to work. However, if you want to make your formulae adapt automatically to changes in the input data, it is often necessary to create more complex formulae.

Automating a formula

For example, if you want to total a column of values by category, you could just use SUM() to include references to each of the cells involved. However, as soon as the underlying data was changed, all of your SUM() formulae might need to be changed to refer to different cells. To automate the process, you could opt to use the slightly more complicated SUMIFS() function. Assuming that you didn’t add any additional categories and that none of the category descriptions changed, the formulae would automatically adjust to changes in the underlying data and, as long as the data was held in an Excel Table, also cope with changes in the number of rows. Moving on to the next stage of automation, by using Excel’s recent Dynamic Array functions, we could even make our formula adjust to changes in the categories in use.

In example 1, we combine the use of the Dynamic Array UNIQUE() function to generate a dynamic list of every item in our Category column with SUMIFS() to total the Value column of each of our Category items, using the Dynamic Array # operator. As you can see in our before and after examples, our formula combination copes with the addition of a new row with the new category of ‘Van’.

Convert US format dates

There is a neat and very simple way to convert a column of dates that have been entered using the M/D/Y format. Select the column of dates in question and, from the Data Ribbon tab, Data Tools group, choose Text to Columns. This command is intended to separate a single column of text into multiple columns. So, in example 2, we could use it to separate our Product column into two separate columns using the – as a delimiter.

However, we can also ignore its ability to separate based on a delimiter or a number of characters and instead use the third stage of the Text to Columns dialog to specify a Column data format of Date using M/D/Y. The effect of this is to convert our single text column into a single date column in the same cells but switching the month and day around.

Admirably simple but not admirably automatic. Because this is a one-off operation, changed or additional entries would need to be processed manually each time.

Coming up with a formula-based, automatic alternative is far from straightforward. Because our dates have been entered without leading zeros for single digit days and months, we can’t predict the start and end of our day and month values just by the number of characters. Instead, we would have to use the FIND() or SEARCH() function to locate the position of the / characters and then base the use of various other text functions on these positions. Here’s one possible approach:

=VALUE(MID(A2,SEARCH(“/”,A2)+1, SEARCH(“/”,A2,SEARCH(“/”,A2)+1)-SEARCH(“/”,A2))&LEFT(A2,SEARCH(“/”,A2))&RIGHT(A2,LEN(A2)-SEARCH(“/”,A2,SEARCH(“/”,A2)+1)))

We can make this a little more elegant using the recently introduced LET() function. This function allows you to allocate up to 126 different calculations to named ‘variables’, which can then be used in the final argument that performs the overall calculation.

In our example we could use LET() to reduce the number of times we use SEARCH() to find the / character by creating a variable for the start of the month digits and the end of the month digits:

=VALUE(LET(StartM,SEARCH(“/”,A2)+1,EndM,SEARCH(“/”,A2,StartM),MID(A2,StartM,EndM-StartM+1)&LEFT(A2, StartM-1)&RIGHT(A2,LEN(A2)-EndM)))

We could use this formula to automatically take our column of US dates and create a corresponding table of UK dates. However, if we just use cell references, our formula is not going to automatically include new rows.

To achieve this, we can use Dynamic Arrays to refer to entire Table columns:

=VALUE(LET(StartM,SEARCH(“/”, MyData[Date])+1,EndM,SEARCH(“/”, MyData[Date],StartM),MID(MyData [Date],StartM,EndM-StartM+1)&LEFT (MyData[Date],StartM-1)&RIGHT (MyData[Date],LEN(MyData[Date])-EndM)))

In this formula, all the references to the individual cell A2 are replaced with references to a complete column in our MyData Table MyData[Date].

Example 3 shows our fully automated solution with a row added to the original Table, resulting in the UK date table updating automatically.

Power Query epic fail

In case you were thinking that Power Query might be a better option, although we could create a new table incorporating a converted date column in about 30 seconds with just 12 mouse clicks, the need to manually refresh our results Table (or wait for up to one minute for an automatic scheduled refresh) means that the method completely fails the fully automatic test, even though the table that Power Query creates could be a dynamic source for a PivotTable or Excel chart in a way that our Dynamic Array table cannot.

Excel is a registered trademark of Microsoft inc. Screen shots reprinted by permission from Microsoft Corporation.