3

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?

Normal Formula

Normal Formula (and result)

Array Formula

Array Formula (and result)

3
  • 1
    I really like this question. Microsoft is inconsistent in this special regards. Every function can somehow be executed via menu - except this one
    – nixda
    Sep 19, 2014 at 5:56
  • @nixda the =max(len) specifically? Sep 19, 2014 at 11:41
  • No, @Raystafarian, he means the conversion to an array formula from a normal formula. Sep 22, 2014 at 7:15

3 Answers 3

6

Excel has a number of native formulas that allow you to create/ utilise arrays, for example

SUMPRODUCT()
INDEX()
OFFSET()    
SUMIFS()
COUNTIFS()
AVERAGEIFS()

Knowing these native formulas well will allow you to avoid the CSE array formulas in most cases (if that is your aim)

For example, try this for numbered ranges:

=LEN(MAX(INDEX(C2:C14,,1)))

Furthermore, as a result of discussion below, this formula will work for all numbered and non numbered/mixed ranges:

=MAX(INDEX(LEN(C2:C14),,1))
8
  • How would you adjust that to =max(len)? Sep 19, 2014 at 13:28
  • @Raystafarian, I've added an example to my post
    – CallumDA
    Sep 19, 2014 at 14:09
  • That is taking the length of the maximum number, whereas the question is taking the longest length of the items in the list. Reversing it to =max(len(index... doesn't seem to produce the same result. Not criticizing, +1 Sep 19, 2014 at 15:08
  • 1
    Curiously, you could write the formula like this, though =MAX(INDEX(LEN(C2:C14),,1)) this will address non-number lists
    – CallumDA
    Sep 19, 2014 at 15:38
  • 1
    You should add that to the answer, that is pretty curious. And it works with non-numbers AND mixed lists! Sep 19, 2014 at 15:41
1

One work-around for this limitation is to create a small VBA Sub, and assign it to a keyboard shortcut of your choice

This will convert the selected range into an array formula.

Sub ConvertToArrayFormula()
    Dim rng As Range
    Dim arr As Range
    Set rng = Selection
    For Each arr In rng.Areas
        arr.FormulaArray = arr.Cells(1, 1).Formula
    Next
End Sub
3
  • Good thought in general, but despite the title of my OP, I clarified my question in the post itself as "without using a hotkey?" Substituting one hotkey for another defeats the purpose. Now, if you can show me how to assign that Sub to a button on the toolbar -- that is just the sort of thing I'm looking for! Sep 20, 2014 at 13:05
  • @MyPetOcean, was adjusting the formula not an option? You didn't explain why you need to be able to enter without using hotkey (I presume by "hotkey" you are talking about Ctrl+Shift+Enter)
    – CallumDA
    Sep 21, 2014 at 11:37
  • I mean I'd like to avoid any hotkey. My initial rationale is long-term simplicity (and memorability) for non-savvy users. But secondly, there just simply ought to be another way to do this. What other major software has important features only accessible via hotkey of all things? Sep 21, 2014 at 12:59
0

One answer is to develop your solutions using Names for both Ranges and Formulas.

Thus, the look of your formula may be improved by defining 'v' to refer to =C2:C14. The formula =MAX(LEN(v)) still needs to be array entered but, if you define 'n' to refer to =LEN(v), the array calculation is carried out as the default behaviour of a named formula. Therefore =MAX(n) works without using CSE entry.

1
  • 1
    For people unfamiliar with using names ranges, it would be helpful to expand your answer a little to include a simple example of how to do it.
    – fixer1234
    Apr 21, 2015 at 20:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .