In this video, we're looking at the scan function. Now, it is one of Excel's more complex functions. Therefore, many users struggle to understand how it works, which means that the goal of this video is to help demystify this function so you know how it works and when to use it.

So, if you're ready, let's get started. Let's start by looking at what the scan function does. It performs a calculation for each value in an array.

And for each calculation, the function can use the current value and the result of the previous calculation. And then the final result returned is the value at the end of each calculation. Now don't worry if that doesn't make any sense.

Soon it will all become clear. Scan has three arguments. The first is initial value.

This is the value to use as the first previous result. The second argument is array. These are the values that we want to perform the calculation for.

Then the final argument is function. This is the calculation that we want to perform for each value in the array argument. Let's take a look at a simple example.

Here we have our three arguments of initial value, array, and function. The initial value is zero. The array contains the numbers 1 2 3 and four.

And the function that we want to perform is the value plus the previous result. For our first calculation, the value in the array is 1. Now we don't have a previous result for our first calculation.

Therefore, we use the initial value, which means 1 + 0 gives us our current result of 1. We then move on to the second value in the array and that is the number two. The result of the previous calculation was 1.

Therefore, 2 + 1 equals 3. We then go to the next value in the array, which is 3. The previous result was 3.

3 + 3 = 6. Finally, the last value in the array is 4. The previous result was 6.

So 4 + 6 = 10. And in that scenario, the scan function would return 1 3 6 and 10 as the result in a single array. So that's how scan works.

Now let's head over into Excel and take a look at an example. On the left we have the values that we are working with. We have a column of dates.

Then we have a column of amounts coming in followed by a column of amounts going out. Using the scan function we can create a running total for these values. In cell E6 I'll type equals scan opening bracket.

Now if we had an opening balance we could enter that number for the initial value. In this scenario, we're saying that we're starting with zero. Therefore, the initial value is zero.

Next, we come to the array argument, and we're going to select the cells from C6 down to D17. Next, we come to the function argument. And this is where things can become a little confusing because how do we tell Excel that we want the current value plus the previous result?

we need to use the lambda function. Lambda enables us to create a function. Now when scan calculates it always passes across two values into that lambda function.

The first is always the previous result and the second is always the current value. So let's create placeholders for those values and we're going to call them previous and value. In this scenario, we want to take the previous result and we want to add the current value.

We can close the bracket on the lambda function and close the bracket on the scan function and then calculate. Now what we have here is a running total. It takes our initial previous result which was zero and it adds the current value of 94.

So 0 + 94 is 94. Next, our previous result was 94. Our current value is - 65.

So that gives us the result of 29. We then move to the next row. The previous result was 29.

Our current value is 51. So 29 + 51 is 80. And then that continues for all the other values in our array.

In our example, we have used the terms previous and value to describe our previous result and the current value. But we can use any terms to describe these. And often it's common to use a which stands for accumulator and then v as a shorthand for value.

So once we update both of those instances in the formula, it now calculates and gives us the same result. Now, it's likely that rather than having the inflow and the outflow, we probably want our array to be a single array of movements. In cell H6, I'm going to type equals and then select C6 to C17.

I'll then add the cells D6 to D17. When that calculates, we now get a single array that only contains the movements. So let's copy that array calculation.

We will then come back into our scan function and let's use that array calculation for our array argument. When we calculate we now get a single column and that's because all of our movements are in an array which is a single column. In our example, we have used a simple calculation of the previous result plus the current value.

But we can also use functions within our calculation. So rather than using a + v, we could use the sum function. Let's use a as our first argument and v as the second argument.

It now calculates and gives us the same result. We've seen that scan passes across two values into the lambda function. The first is the previous result and the second is the current value.

If we have a simple function that also accepts two arguments in that scenario we can use eta reduction. This is where Excel assumes that the first argument passed into the function is the previous result or accumulator and the second value passed into the function is the current value. If this is the type of calculation we want then we can reduce down our function syntax.

Instead of declaring the lambda and then the variables and then the function, we can simply use the function name without any arguments and without any brackets. So let's head back to Excel and let's use eta reduction on our example. Let's edit our formula and rather than using the entire lambda syntax, we can just replace that with sum.

Now depending on the complexity of your calculation, you may not be able to use etera reduction. Even simple calculations can't be performed with eta reduction. For example, what happens if we use count instead of sum?

We might expect that this would count the number of rows in the array. However, we know that scan passes across only two values into the function. It passes across the previous result and the current value.

Therefore, count will always return two because there are only two values in each calculation. The previous result and the current value. Now let's move on and look at a common use case for the scan function and that is corkcrew calculations.

This is where we have a closing balance from the previous period and that becomes the opening balance for the next period. We then have movements and then a closing balance and again that closing balance becomes the opening value for the next period. So therefore we have this corkcrew type effect.

In our example here we have a row of inflows and a row of outflows. So let's start by calculating the movements. I type equals then select D25 to O25 and then we want to add the values in D26 to O26.

When we calculate we now get the net movement for each period. Now let's move down to cell D31 and calculate the closing balance. I will enter equals scan opening bracket.

For the initial value, we want the opening balance, which will be the value in cell C31. The next argument is the array. For this, we want the values starting in D30.

We can enter the hash or pound symbol to get the spill range from that cell. Finally, we come to the function argument. In this scenario, we can use eter reduction.

So I'm going to enter the name of the sum function without any brackets and without any arguments. When we close the scan and calculate that now gives us our closing balances. Now let's calculate the opening balance.

For this we'll type equals. I'll select cell D31 and then hash to get that spill range. From that we want to minus the movements.

So I'll select D30. Enter a hash and then we can calculate. So using simple formulas along with the scan function we are able to create a corkcrew calculation.

So that is the scan function. It's an iterative type function because we always have that previous result to use in our subsequent calculation. Now if you like this video you are going to love our dynamic formulas unleashed course.

It contains everything you need to master dynamic arrays and lambdas inside Excel. Just head over to excelofthegrid. com and check it out.

And once you've done that, I think you should watch this video next. It contains lots more array techniques.