google sheets array formula if This is a topic that many people are looking for. khurak.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, khurak.net would like to introduce to you Google Sheets – Drag Formula Down Automatically – Autofill Arrays . Following along are instructions in the video below:
“So in this video. I m gonna show you how to create an array formula formula that will give you an effect of automatically dragging down your formulas. So you see what i mean in a second. So usually.
When we make formulas seen our spreadsheets like in google sheets. We go here for example. Let s say i have sales and i have quantity maybe i want to divide my sales by quantity to get some number that s gonna be per piece. So the formula doesn t matter here so much the idea here if i did an equal sign.
I could say take this sales number divided by this quantity number and if i enter that s gonna give me whatever. That s supposed to be and if i take that number and if i drag this down. It will just basically give me all of these for all of these rows at the same time. Now.
Let me just convert this to currency really quickly. Here. So. What i want to do i want this to work as we add more data.
I want this to just automatically drag down this formula. So that i m gonna do using an array formula. So what i m gonna do i m gonna just clear all of this and go back and instead of creating this formula as a regular single liner formula. I m gonna use an array and the way i m gonna do that array.
I am basically gonna say let s take all of this basically this entire column of values and i m gonna divide it by this other entire column of values so right now if i d enter you ll see that all i get is the first value out of that list. But i can t change that by wrapping this entire thing in array formula function to explain that this is supposed to work as an array formula and now if i enter you ll see it s dragging this down. But if you look like in the second row right here. There is no formula so if you look here.
It s just a number. The formula is actually just in this first cell. It just outputs the entire array and drags the whole thing down pretty much so that s great. But we probably want to do this in a way that if we add more numbers here the works for this new rose.
Which now as you can see doesn t and there isn t it doesn t that s because when i made this i selected basically the data to go until here and the way i can basically send this all the way down i can just remove my android references from both of this ranges so i ll remove this twelve from here this twelve from here to send both of those all the way down. And if i had enter right now. It will basically send that all the way down the only problem is it s now also gonna go all the way down meaning. It s gonna use all of these as formulas as well.
.
Which is the same thing that would happen if i just made a basic formula this divided by this and then i kept dragging it down more because we re dividing this cell by this cell and in this cell. There is nothing it treats this blank cell as a zero and when we divide number by zero. We get division by zero error. So that s what s happening here so what we want to do we want to somehow control this so that when it goes over this lines with data.
We want to stop these happening and filling up all of these errors in here. So what we can do to make this happen. So let me explain this with a single formula logic. And then we ll try to move that to this a rate logic.
So if i did this with a single formula and i was trying to still do this divided by this and drag it down again. We would get that error. But what we could do instead we could just wrap this entire thing in an if statement instead of just doing this formula right away. Let s just verify and check.
If one of these cells has some content. So i could say if this quantity equals two double quotes. So basically i m checking if that quantity is blank comma. Then we re gonna just keep it.
Blank comma. Otherwise. I can do the formula and the formula was let s take this and divide it by this so using this if statement. We can control that if this cell is blank.
Then let s just leave it blank otherwise. Let s do this so if i had enter right now you ll see dust. But if i drag this formula down by the time we eat here because this cell right here is blank. It s just gonna leave it empty.
So now let s try to do that same thing as an array. So i m gonna go back here and i m gonna remove all of this start over again and it started this array formula function. I m gonna start with an if function open parenthesis the first one is again logical test. But right now we re gonna check against the whole column so i m gonna check if this entire column here and to refer to the whole column.
I m gonna get rid of the end reference that 15 so send it all the way down. I m gonna say if that equals two double quotes. Means blank comma. Then i want to live.
.
It. Blank double quotes. Comma otherwise. I want to take this entire column and i can do that again by removing this and reference and divide.
It by this entire column and again remove the end reference for this one as well close parenthesis first parenthesis is closing my if function right here. I need another parenthesis to close this array formula function so i m gonna do that as well hit enter and that should now send this formula down and you can see how these cells are now staying blank. Because there s nothing going on in here. So now if we go and add something here and and that should work this will also work if we don t type anything here and type here.
Because the condition were checking remember to make sure that this is just not blank. And that created this effect of dragging our formula down based on this so if we remove some of this c. It will also remove that whole dragging down effect. So it will just keep this empty.
So you can do this only for functions that actually do accept arrays and work with a race. It s not gonna work for everything. So for example. If i wanted to make sure that both of these must be filled in for this to work then the regular way to do that would be to do like f.
Do an or function. And say if this equals blank comma or this equals blank so or will control. This is blank or that is blank. This is just closing that or function comma then i want to keep it blank comma otherwise i want to do what i wanted to do.
Which is this divided by this so now i m making sure both of them are supposed to be filled in so if i do this and i drag this formula down this will work as you can see and what s gonna happen if i just type a number here now it s still not gonna do anything. Which is this one. This is that old formula and if i type something in here. You ll see that this starts working because now both of them are filled in now for any of this values.
If they are not here. This doesn t do anything so this was an example of creating an old function. But the problem that is that this is not gonna be something that we can translate to an array. The reason for that is because our function doesn t work with a race.
So if i try to do this in a ray way so just trying to convert this whole thing again. So i m gonna do if write the same way. I did in that formula and i ll say or open parentheses. And in or i m gonna check this equals blank comma or this again remove the end reference equals blank close the or function.
.
Then comma let s keep it blank comma otherwise let s take this whole column and divide. It by this column and remove the end reference close parenthesis for if close parenthesis for right formula hit enter and as you can see it does not work because or function does not accept a race. So what you have to do you have to find some other ways to handle this that do handle a race. So if the function doesn t handle a race.
You cannot do this so the way you can find out if your function. Accepts a raise or it doesn t accept a race is by simply doing this just use the function by itself so if i just took this or function inside of an array formula to test what happens if this we re to work. It should basically output the entire array of in this case truths of forces. But it does not it only gives us the first one which means this function does not accept a race.
So what we can do we can do some trickery here again. I m gonna do this separately on the side and then convert it to an array and i can t simply just check is this equal to blank right and that gives me false. I m gonna take that and wrap it in parentheses. And put double negative in front of it and what that s gonna do it s gonna convert falses to zeroes and choose two ones so if i drag this down.
See basically all of these are not blanks. So i m getting zero. But if i keep dragging down these are ones because these are blanks. So let me try to do that same thing for quantity so i m gonna do equals double negative and in parentheses.
I m going to create my test i m gonna check is that equal to blank and we get our zeros and ones so you can see because this is blank and this is not blank. We get 0 and 1 here so what we could do with this two tests. But i actually get rid of this column. So what we can do i m gonna go back and change.
The logic of this array a little bit so instead of checking if something is blank. I m gonna actually check if something is not blank so instead of equal to i m gonna say is it not equal to blank. So that will just basically switch our ones of zeros and i m gonna do the same thing here. I m gonna say is this not equal to blank.
And that s gonna switch our ones and zeros again so in this case one means. It s not blank. 0. Means.
It is blank and then what i m gonna do i m gonna just do a multiplication. I m gonna say let s take this and multiply. It by this so if i d enter and drag it down. So what s going to happen when we multiply one by one we will get a 1 when we multiply baum by zero we ll get a zero when we multiply 0 by 0.
.
We get a 0. So what s going to happen is that in case is when both of these are filled in we should be getting one here. And if one of them is not we should be getting a zero let me actually add something here. So we can have an example of that to see these are all zeros because of that multiplication and these are all ones ones are the ones when both of them are filled in now i m gonna create this thing with an array so i m gonna remove all this and start over again so the first one was an array formula in my array formula let me actually put that array formula function here so for this array formula function the first one was that first test so it was double negative and it is double negative.
I was checking if this and in this case. It s an array. So it s gonna be the whole column is not equal to blank that s it so if i enter right now it will just output that one zeros for just this column. So anything that s not blank is a one everything that s blank is a zero.
So that s good so now what i m gonna do i m gonna take this and wrap it in parenthesis again to group it together so sorry about that bunch of stuff showed up there. But because you have too many parenthesis those things happen so now i m gonna take that and i m gonna multiply it by a second set of parenthesis and in this parenthesis. I m gonna do that second double negative parenthesis thing and here i m gonna take all of these and again the whole column and check is that not equal to zero. So i m basically multiplying both of those in this a right formula.
I m gonna hit enter and that should give me this actually let me delete a couple of other columns here sorry so now we should have this on one line. So you can see what s going on so that s our multiplication that will only give us one when both of these are filled in and if one of them is missing. It s just gonna give us zero. See this is this is zero.
This is zero these are our ones. So now because we have this and it works similar to what we need to get with our or statement. We can take this whole thing. I m not including this last parenthesis because that s for a rate formula and not the first parenthesis again because it s for a right formula.
I m gonna do control x or command x to cut that and here. I m gonna start my if statement again i want to check if the result is 1. Then i want to do my formula. Because we know both of them are there and otherwise i want to do the other one so what i m gonna do i m gonna paste this test inside of that if statement and i don t have to check if that equals to 0.
Because in if statements ones are automatically true and zeros are false and that s exactly what we used to get 1 and 0. So if that is 1 comma that means we should do the formula so i m gonna grab this column and divide it by this column just like that comma otherwise i wanted to leave it blank so i m gonna do double quotes to leave it blank otherwise. I m gonna close it function with this parenthesis and finally the other parenthesis closing our array formula so we re gonna keep it there and i m gonna hit enter and you should be able to see how i have an array formula that drops this formula down it only works for cells where both of these are filled in doesn t work in all the other cases. I m just gonna delete these columns now because i don t need them anymore so now anytime.
I enter some value here because both of them have a value it will work same thing here. And if i remove any of this it will stop doing this because one of the values doesn t exist so that s a couple of examples of doing automatically auto feeling columns using the right formulas. Hopefully that was helpful thanks for watching please subscribe and i ll see ” ..
.
Thank you for watching all the articles on the topic Google Sheets – Drag Formula Down Automatically – Autofill Arrays . All shares of khurak.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
description:
“Learn how to create array formulas in Google Sheets that will drag formulas down automatically.nn#googlesheets #tutorial”,
tags:
Google Sheets, Drag, Formula, Down, Autofill, Automatic, auto, Arrays
