Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
3 Answers
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))
-
-
-
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 -
1Curiously, you could write the formula like this, though
=MAX(INDEX(LEN(C2:C14),,1))
this will address non-number lists– CallumDASep 19, 2014 at 15:38 -
1You 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
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! 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)– CallumDASep 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
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.
-
1For 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. Apr 21, 2015 at 20:00
=max(len)
specifically?