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

<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.&nbsp; 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.<br><br>Um, what happened, what happened is he created these, uh, cells that would reference the. , um, the form, so the form sheet, right? Are you an A1 or B2 Kind of Sheet Guy? Automatic Calculations What is a Spreadsheet? Anatomy of a Spreadsheet

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.<br><br>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.<br><br>Um, that just gets you an array, which is a, a range of cells . How to create a formula for every row in a column in a Google Spreadsheet? What is a spreadsheet formula?

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.<br><br>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. Figure Out Frequency of Numbers 2 Forms 1 Sheet

Let's do 1 24.<br><br>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.<br><br>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. Figure Out Frequency of Numbers What is a spreadsheet formula?

So what is, what do we have now? 1007 rows. So what happened is every single time that we inserted a new.<br><br>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.<br><br>49. 49. Let's look. There's now another row. It inserted a row, and if there was anything underneath.<br><br>It pushes it down. Track Every Edit (Almost) Writer Better Prompts How To Insert Row Under Header Automatically

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.<br><br>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. 2 Forms 1 Sheet

If you have all of these references . All these references are gone.<br><br>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.<br><br>So, um, so this was a really interesting problem to solve because. It ends up being a feature, not a bug of Google Sheets. Your First Formula in Google Sheets How To Reference Another Spreadsheet in 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 .<br><br>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> Replace 1,000 Formulas Instantly Your First Formula in Google Sheets What is a spreadsheet formula?