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)

  • 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 '14 at 5:56
  • @nixda the =max(len) specifically? – Raystafarian Sep 19 '14 at 11:41
  • No, @Raystafarian, he means the conversion to an array formula from a normal formula. – David Michael Gregg Sep 22 '14 at 7:15
5

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))
  • How would you adjust that to =max(len)? – Raystafarian Sep 19 '14 at 13:28
  • @Raystafarian, I've added an example to my post – CallumDA Sep 19 '14 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 – Raystafarian Sep 19 '14 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 '14 at 15:38
  • 1
    You should add that to the answer, that is pretty curious. And it works with non-numbers AND mixed lists! – Raystafarian Sep 19 '14 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
  • 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! – David Michael Gregg Sep 20 '14 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 '14 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? – David Michael Gregg Sep 21 '14 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
    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 '15 at 20:00

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.