-1

Do you have any suggestions on how to reduce the effort on typing the formula below

={"Header";ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(dosomething(),"-")),COUNTA(A2:A),1)}

I make calculations everyday on several files, and I have to do this for most of the columns to auto-calculate data. I type it several times.

Is there any way to reduce the effort?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
David Salomon
  • 804
  • 1
  • 7
  • 24
  • 2
    Excel or Google sheets? `dosomething()`? – Osm Sep 30 '22 at 02:21
  • @Osm is just an example of a formula – David Salomon Sep 30 '22 at 02:24
  • You want to return the first result of any formula and a header on top? – Osm Sep 30 '22 at 02:26
  • Please edit your question to assign the proper tags, your formula seems to be from Google Spreadsheet. `ARRAYFORMULA` is not an excel function. – David Leal Sep 30 '22 at 02:30
  • You can make a named function `=ArrayFormula({"Header";ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(dosomething,"-")),COUNTA(range),1)})` but without knowing what `dosomething()`? is, its pointless. – Osm Sep 30 '22 at 02:36
  • 1
    I think you are falling into XY problem, see [What is the XY problem?](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Osm Sep 30 '22 at 02:38
  • [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 30 '22 at 06:16

1 Answers1

3

You can reduce it by creating a named function.

Create a function like:

CUSTOMCUT:

Arguments: arr
Description: The array to cut up to the count of Sheet1!A2:A
Formula definition: ={"Header";ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(arr,"-")),COUNTA(Sheet1!A2:A),1)}

Then you can use:

=CUSTOMCUT(dosomething())

Related:

Avoiding traditional usage of arrayformula

TheMaster
  • 45,448
  • 6
  • 62
  • 85