7

I watched a tutorial where the author uses an IF statement along with the ARRAYFORMULA function to add a title row to a column of data. Links are given to the docs; however, for an example of how to use ARRAYFORMULA see this answer.

An example can be seen below:

enter image description here

I was able to populate the C column by placing the following formula in C1:

=ARRAYFORMULA(if(row(A:A) = 1, "spent", B:B - A:A))

I'm confused about the syntax. I understand that X:X references the entire X column but I don't understand how it's being used to check if we're at cell A1 in one context and then being used to apply mass formulas in another context.

  • How does the above line work?
  • Can you illustrate with some examples?
Community
  • 1
  • 1
mbigras
  • 7,664
  • 11
  • 50
  • 111

3 Answers3

26

It sounds to me that the information you learned led you to expect that row(A:A)=1 translates to row A1?

It works a little different than that, the syntax as your using it now, is basically saying if any row in A:A has a value of 1, then write "spent" else subtract B-A

My suggestion:

use a literal array to make your header, then use the if(arrayformula) to only populate rows with values, for aesthetics:

Example:

={"Spent";arrayformula(if(isnumber(A2:A),B2:B-A2:A,))}

Explanation:

The {} allow you to build a literal array, and using a semicolon instead of a comma allows you to stack your cells vertically, following that we check if there is a value in column A, if so, subtract A from B, else leave it blank.

enter image description here

mbigras
  • 7,664
  • 11
  • 50
  • 111
Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • +1 for how you handle the `0`s! I've been using: `if((B:B - A:A) > 0, B:B - A:A, "")` but I like your way with [isnumber](https://support.google.com/docs/answer/3093296?hl=en) better :) – mbigras Mar 07 '17 at 05:19
  • 1
    thanks :) Also btw the reason i technically leave just a trailing comma `,` instead of `,""` is only because `,""` techinically leaves an occupied blank cell, but for some reason leaving only the comma doesn't - i forgot to explain that in my answer – Aurielle Perlmann Mar 07 '17 at 05:21
  • I noticed that also, have you come across situations where the empty string `""` will get you into trouble? I think it would be worth adding to the answer :) – mbigras Mar 07 '17 at 05:21
  • 1
    yeah, from time to time - it especially can throw off counta's when trying to count values,since it will technically count the blank strings as 1 – Aurielle Perlmann Mar 07 '17 at 05:24
  • Thanks so much for sharing this as it is something that has bugged me for ages. I've always ended up having to put an unnecessary extra row under my headers for the `ARRAYFORMULA` – ProNotion Jun 17 '22 at 12:31
0

why not just put the column title directly on the first row cell, and start the array formula from the 2nd row, using the A2:A, B2:B syntax?

If something does not have to be in a formula, better put it directly on the cell - simpler for others to understand what's going on, and the formula will be simpler.

meizy
  • 339
  • 2
  • 4
  • Your post should be an answer rather than a comment. However, the reason is that sorting the table, inserting a row between 1 and 2, or otherwise moving the formula will result in the cells above it not being populated. – Splic Jul 26 '19 at 22:09
0

If you put the array formula in line 2, and someone sorts the data, then the arrayformula will move. If it is in the header line, this is less likely to happen.

You can also use the IFS function to achieve a similar effect to the array,

=arrayformula(ifs(row(A1:A)=1,"Spent",A1:A="",,True,B1:B-A1:A)

Here the first condition checks the row number, and if it is row ONE, then inserts a Column Header.

The Second condition - A1:A="",, - ensures that blank lines are ignored.

The Third condition True (ELSE) performs the calculation.

This method also allows for different calculations to performed on different rows depending on requirements.

Martin Gal
  • 16,640
  • 5
  • 21
  • 39
TFS
  • 1