4

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.

enter image description here

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).

7

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)

  • The answer is not as simple as 'not working with arrays': EOMONTH actually works fine with array literals as input for either parameter, e.g. these formulas work fine: =EOMONTH("1 Jan 2019", {1,2,3}) and =EOMONTH({"1 Jan 2019","1 May 2019","1 Aug 2019"}, 3) and even =EOMONTH({"1 Jan 2019","1 May 2019","1 Aug 2019"}, {1;2;3}). However, when the array comes from a sheet reference, it returns #VALUE, suggesting some internal processing quirk. – Govert Aug 27 '19 at 11:02
  • Did you read to the end, @Govert ? It clearly states: "works with arrays, but not with ranges" – Aganju Aug 27 '19 at 15:18
  • You're completely right - I definitely did not see the last paragraph. Thank you for pointing that out - I now see the "- -" story too which is the most wonderful workaround and indeed works perfectly. – Govert Aug 27 '19 at 15:31
  • Any manipulation of the argument seems to work, including prepending "+" which seems a bit easier on the eye (but still magic). – Govert Aug 27 '19 at 15:46
0

It doesn't work. But you can do:

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}
  • Interesting; I've never seen the day set to zero like that. It's cool that it handles December correctly. But EOMONTH isn't the main issue here -- it's just the most recent thing I've stubbed my toe on because of the array formula problem. What I'm asking about is why it can't be used in array formulae, as part of trying to figure out in general which formulae can and cannot be used in any given array situation. For example, I already know to be wary of trying to convert scalar uses of the booleans into array versions. So ... – tkp Jan 28 '18 at 1:40
  • ... something like: D1=IF(OR(A1,B1,C1),<something>,<something_else>) copied down to row 100 (say) is not going to convert properly to an array formula in D1:D100 of: {=IF(OR(A1:A100,B1:B100,C1:C100),<something>,<something_else>) – tkp Jan 28 '18 at 2:03
  • @tkp In array formulas you can typically simulate "AND" with * and OR with +.....BTW the day in date function (or even the month) can even be set to a negative number, so =DATE(2018,-1,-1) gives 30th Oct 2017 – barry houdini Jan 28 '18 at 14:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.