ROT_45 Function in Excel

=ROT_45(array, [n], [fill_with])

Argument	Description	Example
array	An array or range of numbers or text	A1:E6
n	(Optional) Number of times to rotate (±1, ±2, ... ±8). Defaults to 1.	1
fill_with	(Optional) A value to use to fill empty spaces. Defaults to NA().	"Empty"

⤓ Word Search Example (Filename: word-search-example.xlsx)

Description

The idea for this function came from staring at a Word Search puzzle. It occurred to me that if I could rotate an array 45 degrees, then the diagonal words would become horizontal words, and then it would be really easy to concatenate the arrays to do a word search with the SEARCH function.

I'm not sure what other purpose this function might have, but Word Search was the purpose for which ROT_45 was made.

Here is the result of rotating the above array 45 degrees counterclockwise, as well as the associated addresses. We didn't start with square cells, so the result isn't truly 45 degrees in the strict geometric sense, but the point is that the diagonals have become rows.

Result of Rotating the Word Search Array 45 Degrees

You can post-process the array however you want, but the [fill_with] option allows you to specify a value to fill the empty spaces. The Default is NA(), but in the above example I used "" for blank.

To take this to the next step, a Word Search algorithm would concatenate each row and use SEARCH on each row to find a word, repeating the process for rotations 1, 2, ... 8. You can see this demonstrated in the example file above.

It may be important to realize that although ROT_90 can be used sequentially to eventually return to the original array, ROT_45 should not be used sequentially. Using ROT_45 twice sequentially does not produce the same result as ROT_90. Instead, the optional [n] parameter allows you to choose different rotations. Check out the code if you want to see how it works.

Lambda Formula

This code for using ROT_45 in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet (using the Excel Labs add-in).

Code for AFE Workbook Module (Excel Labs Add-in)

Note: Requires ROT_90 and FLIPLR

/**
* Rotate an array 45 degrees counterclockwise, making diagonals become rows
*/
/*
* Inputs:
*   array: The 2D array to rotate.
*   [n]: (Optional) Number of times to rotate by 45 degrees counterclockwise. Default is 1.
*        Accepts values from 1 to 8 or -1 to -8 for multiples of 45° rotations (e.g., 1=45°, 2=90°).
*        The rotation is modular, so values beyond 8 are reduced to their remainder modulo 8.
*   [fill_with]: (Optional) Choose to fill empty spaces with a value. Default is NA().
*
* Outputs: 
*   - Returns the rotated array, depending on the number of rotations.
*   - For odd rotations (e.g., 45° or 135°), diagonals become rows and columns.
*   - For even rotations (e.g., 90° or 180°), the ROT_90 function is used.
*
* Notes:
*   - Uses modular rotation logic to handle rotations beyond 360° (e.g., 9 = 45°).
*   - Empty spaces are filled with #N/A.
*   - Requires supporting functions `ROT_90` and `FLIPLR`.
*   - IMPORTANT: This algorithm does not work in sequence. ROT_45(ROT_45(array)) is not ROT_90(array).
*/
ROT_45 = LAMBDA(array, [n], [fill_with],
LET(doc, "https://www.vertex42.com/lambda/rot_45.html",
    // Default values for optional parameters
    n, IF(ISBLANK(n), 1, MOD(n, 8)), // Rotations cycle every 8 steps

    // Array dimensions
    rows, ROWS(array),
    cols, COLUMNS(array),
    new_height, rows + cols - 1,

    // Generate the rotated array based on the rotation step (n)
    rotated, SWITCH(n,
        // 45° counterclockwise rotation
        1, MAKEARRAY(new_height, cols,
                LAMBDA(r, c,
                    IFERROR(INDEX(array, IF(r - cols + c = 0, -1, r - cols + c), c), NA())
                )
            ),
        // 90° rotation
        2, ROT_90(array, 1),
        // 135° counterclockwise rotation. Same as 315° flipped.
        3, FLIPLR(MAKEARRAY(new_height, cols,
                LAMBDA(r, c,
                    IFERROR(INDEX(array, IF(r - c + 1 = 0, -1, r - c + 1), c), NA())
                )
            )),
        // 180° rotation
        4, ROT_90(array, 2),
        // 225° counterclockwise rotation. Same as 45° flipped.
        5, FLIPLR(MAKEARRAY(new_height, cols,
                LAMBDA(r, c,
                    IFERROR(INDEX(array, IF(r - cols + c = 0, -1, r - cols + c), c), NA())
                )
            )),
        // 270° rotation
        6, ROT_90(array, 3),
        // 315° counterclockwise rotation
        7, MAKEARRAY(new_height, cols,
                LAMBDA(r, c,
                    IFERROR(INDEX(array, IF(r - c + 1 = 0, -1, r - c + 1), c), NA())
                )
            ),
        // 0° or 360° just returns the original array
        array
    ),
    result, IF(ISOMITTED(fill_with), rotated, IFNA(rotated, fill_with)),
    result
));

ROT_45 Example

Example: Word Search

Download the word-search-example.xlsx file at the top of this page to see the ROT_45 function in action. It includes a fully functional WordSearch(array,"Word") function as well.

Example: Sum the Diagonals

The MECC version of Harry Gross's "Yellow Brick Road" challenge from January '25, had the following bonus question: "Sum the diagonals in each direction of the yellow brick road. What is the highest sum across possible diagonals?"

There are certainly many ways to do this, but using ROT_45 (along with ROWSUM) was pretty fast and convenient.

Click to Enlarge

=MAX(ROWSUM(ROT_45(_road,,"")),ROWSUM(ROT_45(_road,-1,"")))
Result: 4606

ROT_45

Description

Lambda Formula

Code for AFE Workbook Module (Excel Labs Add-in)

ROT_45 Example

See Also

Follow Us On ...