“ARRAYFORMULA is a function for handling array formulas. For example, something like this.”
Saito slowly began writing out mathematical formulas on my leg cast with a marker. Stop it, I’m not your classmate from your junior high school club. I can’t read it at all.
“Um, Saito-san.”
“I’m writing now, so wait a minute, and don’t move.”
“No, I can’t even read it even if you write it there……”
Saito entered into deep thought for a while.
“Sorry, I think I’m a bit tired. Inoue, please teach it for me.”
“Yes, I understand. I’ll write out an example first.”
Saying that, Inoue used the walls of the hospital room as a sheet and started writing up a table before explaining.
R\C | A | B |
１ | 1 | |
２ | 2 | |
３ | 3 | |
４ | 4 | |
５ | 5 | |
“Suppose you have a table like this and you write this formula in B1”
=ARRAYFORMULA(A1:A5+1)
“This would be the result”
R\C | A | B |
１ | 1 | 2 |
２ | 2 | 3 |
３ | 3 | 4 |
４ | 4 | 5 |
５ | 5 | 6 |
“Eh? The results expanded to B5?”
“Yes, it’s similar to how SPLIT works.”
I see. So such a thing was possible. It’s amazing to be able to execute a formula and have the results reflected in multiple cells.
“You can also combine columns by matching the number of arrays. For example, if you write this in C1.”
=ARRAYFORMULA(A1:A5*B1:B5)
R\C | A | B | C |
１ | 1 | 2 | 2 |
２ | 2 | 3 | 6 |
３ | 3 | 4 | 12 |
４ | 4 | 5 | 20 |
５ | 5 | 6 | 30 |
“You can also use functions inside of it. For example, if you use the POW function in D1 like this.”
=ARRAYFORMULA(POW(A1:A5,B1:B5))
R\C | A | B | C | D |
１| 1 | 2 | 2 | 1 |
２| 2 | 3 | 6 | 8 |
３| 3 | 4 | 12 | 81 |
４| 4 | 5 | 20 | 1024? |
５| 5 | 6 | 30 | ??? |
It seemed like Inoue had used the wrong function for the example. She thought about 4 to the 5th power for about 30 seconds before writing a number down without much confidence. As for 5 to the 6th power, she gave on it completely.
“So, something like this! Do you get it now?”
“Yes, I understand the basic structure. Can I also use REGEX functions like LEFT and RIGHT?”
“Yes, you can also use string functions.”
Inoue began writing another table.
R\C | A | B |
１ | INOUE | |
２ | SAITOU | |
３ | TAKAHASHI | |
“If you have string like this and write this function into B1.”
=ARRAYFROMULA(LOWER(A1:A3)
R\C | A | B |
１ | INOUE | inoue |
２ | SAITOU | saitou |
３ | TAKAHASHI | takahashi |
“It will end up like this.”
Inoue finished her explanation in satisfaction.
“That’s convenient. I suppose you could also use VLOOKUP, SUMIFS, or anything else.”
“That’s not the case. You can use VLOOKUP, but you can’t use SUMIFS. It’s complicated when the function syntax already includes a range. For example, SUM.”
R\C | A | B |
１ | 1 | 2 |
２ | 2 | 3 |
３ | 3 | 4 |
４ | 4 | 5 |
５ | 5 | 6 |
“Suppose we write a SUM, which adds columns A and B in each row of the table, like this.”
=ARRAYFORMULA(SUM(A1:A5,B1:B5))
“I think most users would expect something like this.”
R\C | A | B | C |
１ | 1 | 2 | 3 |
２ | 2 | 3 | 5 |
３ | 3 | 4 | 7 |
４ | 4 | 5 | 9 |
５ | 5 | 6 | 11 |
“But actually, everything from A1 to B5 would be added to C1 instead, umm…35 would be the value, and nothing would happen from C2 to C5”
“Huh? Why?”
“I think that it sees SUM(A1:A5,B1:B5) ends up adding all the numbers from A1:A5 to B1:B5. ARRAYFORMULA can’t interpret whether the range it was supposed to process is the SUM as a whole or the range within the SUM. It won’t get an error, but it won’t work in the way that is expected either. On that note, SUMIFS doesn’t work, but SUMIF does for some reason. There are also other functions that don’t work well, so this function is still in its infancy.”
I see, it’s quite difficult, but I’m sure I’ll come across it in the future. I’m glad to hear about it.
On a side note, the wall was not a whiteboard and the writing didn’t disappear. As a result, I spent a week in the hospital looking at the tiles on the wall with these tables and formulas written on them.
※Functions
ARRAYFORMULA https://support.google.com/docs/answer/3093275
POW https://support.google.com/docs/answer/3093603
LOWER https://support.google.com/docs/answer/3094083