excel-lambda-ROTATE: rotate an array in Excel

The lambda described in this post is in the LAMB namespace, the gist for which can be found here.

The goal

TRANSPOSE is great. But sometimes it doesn’t do everything I’d like. 

So, the goal here is:

Create a lambda function that will rotate an array by 90 degrees an arbitrary number of times

 A solution

Here’s a function I’ve added to the LAMB namespace which I call ROTATE:

ROTATE = LAMBDA(arr,times,[iter],
    LET(
        _times,MOD(times,4),
        IF(_times=0,arr,
            LET(
                _iter,IF(ISOMITTED(iter),1,iter),

                _cols,COLUMNS(arr),

                _rotated,INDEX(arr,SEQUENCE(1,ROWS(arr)),_cols-SEQUENCE(_cols)+1),

                IF(_iter=_times,_rotated,ROTATE(_rotated,_times,_iter+1))
            )
        )
    )
);

ROTATE takes these parameters:

1. arr – an array you want to rotate
2. times – a non-negative integer representing the number of times you want to rotate the array anti-clockwise by 90 degrees
3. [iter] – optional – this parameter is used as a counter by the recursion in the function. It’s not necessary to set this parameter when calling the function from the workbook.

This is what it does:

You can see that for each increment in the “times” parameter, the array from the previous increment is rotated by 90 degrees in an anti-clockwise direction. 

It’s that simple. 

If that’s good enough for you, and you want to use it, please import the LAMB namespace and use it. 

If you’d like to understand how it works, please read on.

How it works

As a reminder, here’s the code again:

ROTATE = LAMBDA(arr,times,[iter],
    LET(
        _times,MOD(times,4),
        IF(_times=0,arr,
            LET(
                _iter,IF(ISOMITTED(iter),1,iter),

                _cols,COLUMNS(arr),

                _rotated,INDEX(arr,SEQUENCE(1,ROWS(arr)),_cols-SEQUENCE(_cols)+1),

                IF(_iter=_times,_rotated,ROTATE(_rotated,_times,_iter+1))
            )
        )
    )
);

As usual, we start by defining variables with LET:

• _times – here we calculate the remainder after dividing the times parameter by 4. This converts any integer greater than or equal to 4 to a value between 0 and 3. For example, MOD({0,1,2,3,4,5,6,7,8},4) = {0,1,2,3,0,1,2,3,0}. The reason for doing this is that a rotation 4 times, or 8, or 12 etc is equivalent to not rotating the array at all. It’s useful to just skip the recursion in those cases and simply exit the function returning the input array arr if the value in _times is 0. And you can see that’s exactly what’s done on the row beneath the call to MOD:

        _times,MOD(times,4),
        IF(_times=0,arr,
            ...

So, if the value in the times parameter is equivalent to “no rotation”, then return the input array. Otherwise:

            LET(
                _iter,IF(ISOMITTED(iter),1,iter),

                _cols,COLUMNS(arr),

                _rotated,INDEX(arr,SEQUENCE(1,ROWS(arr)),_cols-SEQUENCE(_cols)+1),

                IF(_iter=_times,_rotated,ROTATE(_rotated,_times,_iter+1))
            )
        )
    )
);

Again, define some variables with LET:

• _iter – here we check if the iter parameter has been omitted. This should always be the case when calling the function from the workbook. If the parameter is omitted, we set _iter to 1. Otherwise, we use the value in the iter parameter (which has been passed into the function from a prior iteration). This variable is a counter. It iterates by one each time the ROTATE function is called, whether from the workbook or from within the ROTATE function, as we’ll see below. 
• _cols – we get the count of columns in the input array arr. This is convenient since this count will be used twice in the line below. 
• _rotated – here we use the INDEX function to restructure the input array and rotate it by 90 degrees. The input array might be the original array on the spreadsheet, or it might be the result of a prior iteration. To understand how this works, consider the following example:

Essentially, it’s the orientation of the arrays passed into the second and third parameters of the INDEX function that achieves the result. Since we pass a one-row array into the row parameter, we return the values from those rows as a column. Similarly, since we pass a one-column array into the column parameter, we receive the values from those columns as a row. 

• Finally, we check if _iter is equal to _times. If it is, the work is complete and the most recent calculation of _rotated is returned to the workbook. If _iter < _times, then ROTATE is called again with the most recent calculation of _rotated passed as the parameter arr, the _times variable passed into the times parameter and _iter+1 passed into the iter parameter. In this way, eventually we will encounter _iter = _times and the function will exit. 

And that’s it!

In summary

We saw how to create a lambda function to rotate an array in Excel. 

By using recursion and an iteration counter, we can repeatedly apply rotations of 90-degrees anti-clockwise as many times as requested to achieve the result we want. 

Thanks for reading. 

I originally wrote this lambda to support rotating a stem-leaf chart such that the leaves are columns rather than bars, but I’m hoping this ROTATE lambda will come in useful in the future for other projects. 

Let me know what you think in a comment below.