In Excel, exactly why doesn't EOMONTH() (and others) not work in array formulae?

Question

Whether an Excel expression can be converted to the array form is sometimes hard to predict. For example, the following screenshot shows a table with a row of dates at the top, followed by four rows that reference the first. The first three (i.e. Excel rows 3, 4, and 5) each contain an array formula. The first two are fine, but the third, using EOMONTH(), breaks. Then Excel row 6 shows the same EOMONTH() formula, but in non-array form. It works fine. In B3:B5 and C7:I7 I've used FORMULATEXT() to display the functions in the various rows and cells.

I don't see anything obvious in the documentation for EOMONTH() that would predict the failure.

Is there a way to figure out which functions have this kind of limitation? (This is ostensibly the general form of my question, but it doesn't really help much).

Answer 1

Microsoft simply decided to not extend all formulas to work with arrays.

Some might have been difficult, some might have been illogical, or maybe they ran out of time, who knows. As a result, there is a list of functions that can handle arrays, and the rest can’t. I don’t have a link right now (I’ll go look), but it is a fact that there is a defined list.

For many functions, you can google workarounds, for example instead of using EOMONTH(range,0) you can use DATE(YEAR(range),MONTH(range)+1,0)} (the zeroth day of the next month is simply one day before the first day of the next month...), instead of OR(cond1,cond2,...) you can use IF(cond1+cond2+...>0,.., and for AND(cond1,cond2,...) you can substitute IF(cond1*cond2*...,..., because Excel treats TRUE as 1 and FALSE as 0.

Some formulas (the ones that were in the Analysis ToolPak in Excel 2003) can also be made to work with arrays, but not with ranges - subtle difference! Add - - (minus minus, but no blank) before the range to make it an array: EOMONTH(- -range,0)

Answer 2

It doesn't work. But you can do:

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

Answer 3

As well as the workaround noted by others here

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

you can create an array based UDF. In other words, fake it!

The excel workbook must be Macro-enabled for UDF to work (which may or may not be an issue for you). Some resources on array-based UDFs:

• myonlinetraininghub (I found the language in this one easy to understand).
• Here on SE
• Another SE one - this discusses the input as a range or an array. The example in the answer uses two UDFs, but by good use of TypeName, you could actually combine them into a single UDF that knows if a range or an array has been passed.