# Excel SCAN Function Dynamic Spilled Array Formula for Automatic Bank Reconciliation. EMT 1922. ## Метаданные - **Канал:** excelisfun - **YouTube:** https://www.youtube.com/watch?v=Rlp3GIWKu6s ## Содержание ### [0:00](https://www.youtube.com/watch?v=Rlp3GIWKu6s) Segment 1 (00:00 - 05:00) Welcome to Excel Magic Trick 1922. And in this video, we're going to see how to create a dynamics build array formula for bank reconciliation. Now, last video 1921, we did formulas where we entered them into a cell and copied them down. But dynamic spill array formulas, they're going to be totally different. And why do we have to do two different formulas for my book balance and a bank balance? Because anytime you have an account, a bank account, a credit card account, there's two people watching that account. Now, all the items we enter into our books, our formula is going to have to look at every single one. But the bank balance, if there's not a Y in this column, for example, that deposit of 8,200, the bank formula cannot account for it yet until we put a Y and then the formula updates. All right, let's see dynamic spill array formulas. Now, here's the formula we did last video. And this formula is called a single input, single output formula. Notice we're looking at single cells, including the cell directly above, which is always going to be the previous balance, single inputs, and the formula delivers a single answer. But when you have dynamic spilled array formulas, we're going to create a single formula, and it will automatically spill the results down the column. We don't have to manually copy. And later when you have to edit, you only edit in a single cell. So I entered this. Notice I have to manually copy it down. Go to the last cell. Check F2 to make sure all the cell references are pointing in the right place. And then if I want to edit, I come to the top cell. F2. Let's just say I want to say no B for no balance. I have to enter the formula and recopy it down. But with dynamic spell array formulas we just create a single formula and bam it does the rest. Now controll Z F2. Often we can simply take a formula like this single input single output copy escape come over here F2RLV and change the single inputs to the entire array. F4 that's going to be the entire in column minus E4 that's the out column. But the problem with this is aggregate functions like sum, max, min, average, they can never spill a dynamic array. Why? Because the definition of aggregate is take a bunch of inputs and deliver a single answer. So if I try this, well, it's going to spill, but it gives me one single answer for the whole column, the last number right there. Now, Microsoft knew that there was this dynamic spilled array problem with aggregate functions, so they invented functions like by row and scan. Now, scan is the function we're going to use because scan can give us a running total always looking at the previous balance in the array of numbers that we give it. Now, when we use scan over here, we're not going to say in cell minus out cell, we're going to convert these two columns to a single column and then scan will just calculate a running total. And how I do that is if I subtract the whole out column from in I get a single column with positive 5,395 - 31 - 37. 95 - 4,200 plus 1125 and so on. And once we have not two columns but a single column, we can just use scan to get a running total. Now this is a dynamic spill array formula. Normally with these types of formulas over here, single input, single output, you have to highlight all of them and then hit delete. Watch this. If I just delete the top cell, it deletes only that single formula. Ctrl Z to undo. But this is a dynamic spill array formula. So when I delete it, deletes the only formula. We entered the formula in the top cell that was spilling everything. So now we simply equals in the whole column minus out the whole column and when I hit enter there's our single column. Now we can just say hey scan please do a running total. Now dynamic spill array formulas are much different than single input single output. I want you to click ### [5:00](https://www.youtube.com/watch?v=Rlp3GIWKu6s&t=300s) Segment 2 (05:00 - 10:00) in the top cell and look. And in the formula bar you can see there's a formula in every single cell. But with dynamic spilled array formulas, that formula only lives in the top cell. All cells below have ghost formulas. And those ghost formulas mean there's nothing in the cell. In fact, if you F2, there's nothing there. Yeah, that formula only lives in the top cell. F2. That's what makes dynamic spill array formulas so convenient. We're only dealing with a single cell whether we're creating or editing. Now, that number right there, F2. What? Nothing there. Well, I'm free to reference that minus 3795. And from that little ghost right there, the formula knows to go and get it and bring it over to whatever other formula you're using. Controll Z. All right. Now, we can do our running total with scan. F2 after the equal sign scan. Now we need an initial value and over here we had to click one cell above and when we manually copied down it always looked at the previous one. But since we have a single column here if we put the sum function into the function argument of scan it'll always add the previous value to the current value for every row. And that's exactly what we want. So the initial value here is not one cell above. It's just zero cuz guess what? What does that do? It says 0 + 5395. Well, that's 5,395. When it gets to the second row right here in the dynamic spilled array, it's going to be looking at the previous value and adding the minus31. That gives us the correct balance. Now, there's the array. We had to minus those two columns, comma. And now for function, we use the sum function. And [clears throat] that's our formula. Close parentheses. Now I'm going to enter. You could see it works perfectly. We'll turn this off down there. There's the ghost. Formula lives only in the top cell. Now to turn this off. Notice I'm going to edit. F2. After the equal sign, if is number and I highlight the entire column. And it better be exactly the same number of rows and columns as the other arrays. There's the value close, comma. If I click on logical test and hover, all of those TRs, those pick out the spilled numbers. The falses, we want to hide all the numbers down there. So we come to the end, that's the value of true. That's the number we want to see, comma, value of false, double quote, double quote. That's a zero length text string. That's the syntax to show nothing in a formula. Close. Controll enter. And that is a thing of beauty. Notice I did not have to enter the formula and recopy it down. That edited formula just spilled the results. And if we test it, when I put a date here, the formula better show up. 12 slash3. Tab. Sure enough, it does. I'm going to subtract $1. And bam, that formula is working. Now for the bank balance, we have to exclude values when there's not a Y in the reconcile column. So we just have to amend this. And watch this. I'm going to copy this in edit mode. Ctrl C. Tab. F2. Put it in edit mode. CtrlV. Now remember, if I highlight array and hover, those are all the numbers. But I want, for example, that $8,27. 36. That should either be zero or in our case, we're going to use an if function to put false there. And since false is ignored by the sum function, the running total will ignore it. So right before the array I say if what for logical test reconcile are any of you equal to in quotes why because that's text any of those come out true well then I want value of false otherwise well guess what I don't even have to put that argument in I could put a zero but it's a wasted keystroke stroke backspace because if I leave value of false omitted a false will be put in and since the sum function ignores false I'm leaving it as is close parentheses and if we highlight this there it is there's the array we want notice in the sixth position that $8,27. 36 that is not there false is there sum will ignore it and that's our formula ### [10:00](https://www.youtube.com/watch?v=Rlp3GIWKu6s&t=600s) Segment 3 (10:00 - 10:00) when I enter the correct values spill down the column top cell. There's the formula. All the remaining ones are those amazing ghosts. Part of the dynamic spilled array formula. F2. It's not really there, but it is. Equals. If I needed it in some other formula, I reference it and bam, there it is. All right. So, last video we did single input, single output for my book balance and bank balance. This video we did dynamic spilled array formulas using scan if and is number and then for the bank balance we had to add an extra if. All right we'll see you next magic trick. — --- *Источник: https://ekstraktznaniy.ru/video/44592*