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 (𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘵𝘩𝘦 𝘸𝘦𝘦𝘬 𝘯𝘶𝘮𝘣𝘦𝘳 𝘪𝘯 𝘵𝘩𝘦 𝘺𝘦𝘢𝘳) and MROUND (𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘢 𝘯𝘶𝘮𝘣𝘦𝘳 𝘳𝘰𝘶𝘯𝘥𝘦𝘥 𝘵𝘰 𝘵𝘩𝘦 𝘥𝘦𝘴𝘪𝘳𝘦𝘥 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦) 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 𝘃𝗮𝗹𝘂𝗲_𝟭 argument. State {𝟭} as the 𝗶𝗻𝗱𝗲𝘅_𝗻𝘂𝗺 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.
—
𝗜𝗺𝗮𝗴𝗲 𝗡𝗼𝘁𝗲𝘀
• 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘈1 𝘢𝘯𝘥 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘉1 𝘶𝘴𝘦 𝘵𝘳𝘢𝘥𝘪𝘵𝘪𝘰𝘯𝘢𝘭 𝘧𝘰𝘳𝘮𝘶𝘭𝘢𝘴.
• 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘈2 𝘢𝘯𝘥 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘉2 𝘳𝘦𝘵𝘶𝘳𝘯 #𝘝𝘈𝘓𝘜𝘌 𝘦𝘳𝘳𝘰𝘳𝘴 𝘢𝘴 𝘵𝘩𝘦𝘺 𝘳𝘦𝘧𝘦𝘳𝘦𝘯𝘤𝘦 𝘮𝘶𝘭𝘵𝘪-𝘤𝘦𝘭𝘭 𝘳𝘢𝘯𝘨𝘦𝘴.
• 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘈3 𝘢𝘯𝘥 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 𝘉3 𝘳𝘦𝘵𝘶𝘳𝘯 𝘴𝘱𝘪𝘭𝘭𝘦𝘥 𝘳𝘢𝘯𝘨𝘦𝘴 𝘸𝘪𝘵𝘩 𝘵𝘩𝘦 𝘩𝘦𝘭𝘱 𝘰𝘧 𝘵𝘩𝘦 𝘊𝘏𝘖𝘖𝘚𝘌 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯.
