Member Asks: How do I calculate from a set of Form entries?

About this Tutorial

Use arrayformula to deal with Google form entries on a sheet. Even as they keep getting added.

Featured Formulas

ARRAYFORMULA()

Video Transcript

<div> Hey, sheet shakers. It's your friendly neighborhood spreadsheet specialist here with a weird question and even weirder head of hair. I mean, weirder answer. So, uh, a reader had a question. He wanted to, he has a bunch of numbers among other things. I just made this test form for.  this purpose. You get a bunch of data from a form and then you're trying to calculate it, but like you have more data coming in, that happens a lot. Um, what happened, what happened is he created these, uh, cells that would reference the. , um, the form, so the form sheet, right?

And as a new entry would come in, it would push these, these cells down so he would never be able to get the actual information. Now, the, the answer, super simple. Uh, you can do this one of two ways. You can use array formula for the entire, um, column. Uh, so you're just grabbing the reference of the entire column and then, Or you can even do it with, instead of just array formula with curly brackets. So you, you get the entire column of, of your form sheet and you put curly brackets around it to get the array. Um, that just gets you an array, which is a, a range of cells .

And that's the answer, the, the question though. Brings up a really funny or interesting thing. Okay, so let me go through the question again. So basically what's going on is that every time a new form submission is done, this like numbers is getting pushed down. So you can see here, um, like the third one here is numbers B3, then B seven, right? So we skipped four because we had like four. Um, entrances to the sheet. Well, here, I'll even show you again. So I'm trying to like get all of the, uh, numbers here. Uh, this is just a test form. Submit another answer.

Let's do 1 24. So let's look for 1 24. It's not here it is over here. And our. Number now number is bk, right? So, but in the array formula we have 1 24. So why, what is it? What is going on here? Well, it it, it's not a bug, it's a feature. Uh, what happened is that when you create a form, Or rather a form submission, it puts it onto the numbers or the form sheet. It inserts a row and then it pushes all of the other rows down. So we had at the beginning, I'm gonna guess here cause I haven't actually seen this. At the beginning of a sheet, we have 1000 rows.

So what is, what do we have now? 1007 rows. So what happened is every single time that we inserted a new. Answer, it actually pushes everything down. You can actually see the hair. Let me do something. Let's see, let's, let's just. Okay, the sidebar was getting in my way. So let's delete all of these. Delete rose eight, and then, so there's no rows there, right? We want to create another, uh, response. Let's do and numbers. 49. 49. Let's look. There's now another row. It inserted a row, and if there was anything underneath. It pushes it down.

So like let's say we've put in another number . We like go on here and then we go here. Let's sum submit another response. Let's see where it shows up. Let's do 1, 2, 3, submit. Let's go see where it shows up. It is now above the two that we entered. So see, this is like a, uh, it's like a safety feature. It doesn't overwrite, uh, it doesn't, um, delete anything you put in here if you put in your own, uh, typed in numbers here or got 'em in any other way. It is gonna insert. So it's sort of a safety feature of forms , but it ends up really messing with your head.

If you have all of these references . All these references are gone. Cause I deleted, I deleted the rose they were referencing. And now we have 'em back. But this array formula , when we have array numbers, b2b, it has everything in there. We don't have to touch it ever again. We got all our numbers. We can calculate our numbers in another sheet. Um, same over here. If we just used the curly brackets, boom, we got it. So, um, so this was a really interesting problem to solve because. It ends up being a feature, not a bug of Google Sheets.

And hopefully you can use this in a number of ways if you wanna calculate some numbers or do something with some data . Uh, once you have a form and you don't really wanna like, keep at having to go check the form time and time again, just grab the entire column . Uh, use this array formula or use this, um, curly brackets is gonna get you where you're going. It's gonna insert there and just push everything down. You'll never have to touch it again. Hopefully that helps bite.</div>