Non-Dynamic Array Functions
Many Excel functions are dynamic array-friendly, meaning they are capable of returning a spilled range. This isn’t just true for newer ones such as FILTER, SORT, and UNIQUE, but others as well like MATCH, LEFT, and LEN.
Unfortunately, some aren’t. For example, WEEKNUM (returns the week number in the year) and MROUND (returns a number rounded to the desired multiple) can only return single values, so using a range results in a #VALUE error.
=WEEKNUM(F3:F12,2) ❌
=MROUND(F3:F12,0.5) ❌
To circumvent the issue, nest the CHOOSE function and include your range in the value_1 argument. State {1} as the index_num to ensure the values spill down one column.
=WEEKNUM(CHOOSE({1},F3:F12),2) ✅
=MROUND(CHOOSE({1},F3:F12),0.5) ✅
Research suggests the affected functions were originally part of the Analysis ToolPak. However, they have been integrated since Excel 2007, making the installation of the add-in unnecessary.
If you have noticed this behaviour with other functions, please say which ones. I’m hoping the Excel team rectify it in due course.
—
Image Notes
- Example A1 and Example B1 use traditional formulas.
- Example A2 and Example B2 return #VALUE errors as they reference multi-cell ranges.
- Example A3 and Example B3 return spilled ranges with the help of the CHOOSE function.
