Okay, I've got another example, a third example on creating a VBA array function called residuals that's going to fit the data to a line. So I've got some x y data here. We have the independent variable x, which is along the x axis here, and I've got y data. So this would be experimentally observed data. We're going to fit the data to a line using the slope and intercept functions in Excel. Then our array function is going to calculate something known as the residuals of all the data points. And then it's going to output all those residuals, the residual vector back to Excel. y is the observation, that's the y value of the observations, so those are the blue markings. We have the line of best fits, which is the blue dotted line here. The slope is m, b is the y-intercept and y hat here, in y hat equals mx plus b is the predicted value based on the equation for the line of best fit. So we're going to use the Excel functions for slope and intercept to calculate m and b. We can then calculate the predicted value for each value of x here. We have a total of nine of those. Then what we're going to do is we're going to create something known as the residuals for each of those nine different points. The residual for each point, the residuals are just the difference between the prediction and the observation. And then that's what we want to output then as a vector. We're going to have nine of these as a common vector, we want to output that back to Excel. So, I'm going to draw a flow chart for this, we start. The first thing we're going to do is we are going to import the data. So I'm going to define a data array, it's going to be an n by two. The first column's going to be the x values, the second column's going to be the y values. I'm also going to keep track of the things that we need to dim over here. So data is something we're going to need to dim. And we're going to dim that as a variant. Whenever you just say data equals arrange for example, you need to dim that vector or array as a variant. Next, we're going to count rows we're going to store that in a variable n, n is going to be an integer. Next, we're going to ReDim and rows will going to have a couple more vectors in here which I will define in a little bit. We're going to ReDim all vectors. One of the vectors we're going to create is just going to be the x values then we're also going to create the y values. So we're going to extract the first column of data to find out as our x vector. We're going to extract the second column of our data array and define that as the y values. These are both going to be doubles. We're going to use a for loop to import the x and y columns from data. The x vector is just the first column of data. The y vector is going to be the second column of the data. So data is just going to be a direct import from the cells on the spreadsheet. When we're done importing all of those values, then we are going to move along and calculate the slope and intercept. We're going to use the worksheet functions, the Excel functions, slope and intercept to calculate m and b. We have to dim a couple more things. After we calculate the slope and intercept, we can go back to our equation. We're going to now use the line of best fit here to calculate a new vector, I'm going to call yp for predictions, so that's the predict value. We're just going to take each x value. We're going to multiply it by the slope, and add the intercept to create a vector of predictions. So we're going to enter in to a for loop, we're going to iterate through all of the items, we're going to create the yp vector. The next step then is to create a vector of the residuals. We're going to define that as an e vector, and that's just the difference between yp and y. So we create e(i,1) = yp(i,1)- y(i,1) so we're going to get a total of n items, we're going to get n residuals. When we are done with that, the only thing we have to do is to, we just need to output it. So when we're done, all we're going to do is output and then we end. A couple of other things we need to dim over here. All right, so what this will look like in VBA. We create our function residuals. We're going to have an argument which is going to be the range on the worksheet. We've got to dim a bunch of things. So we have one, two, three, four, five, six, seven, eight, nine different things that we need to dim, including one, two, three, four, five different vectors or arrays. So I've dimmed my nine different things, we have i as an integer, n as integer, n is the number of rows. Data is a variant, you have to use a variant when you're using the technique that I'm doing where you just say data equals range. We dimmed x, so this is the vector of x values which of course bind to our column of x values here on the spreadsheet. y is it going to be a vector of doubles so that's going to be equal our column of y values on the spreadsheet once we do the extraction. yp is going to be our prediction, m is the slope, b is the intercept, the y intercept and e is going to be our vector of residuals. After our dim statements, we have to import the data, we're going to count the number of rows and then we're going to do all the ReDim statements. So we can say data equals range to just import this entire range here into the data array. We can count the number of rows of of data or range, they are equivalent. Next I'm going to do other ReDim statements. So I've ReDimmed x, y, yp and e. Now that I know the size, the next step then is to import from data our x vector and y vector using a for loop. So we implement the for loop and we extract or import the data from, we calculate x as the first column of data, y as the second column of data. Now, the next step in the flow chart is to calculate the slope and intercept, and then we're going to apply the slope and intercept to all of our x values to create the y prediction. We're going to then calculate the difference between each y prediction and observation to calculate the vector e and then we're going to output that to the spreadsheet. So we can use the worksheet function slope, so there's an excel function slope we have ys and your xs as a vector. Do the same with the y intercept, there's an intercept function, so we calculate b. And then we enter into the second for loop, where for each i going from 1 to n, we calculate the predicted which is just the slope times the x value plus the intercept. Then we calculate the residual for each i. And then the only thing we have to do now is output. And the output of a function is just the name of the function. So I would just say Residuals = e. So I think we're ready to go. I'm going to go ahead and put a break point here and lets go over to the spreadsheet. So I'm going to type in =Residuals of our x, y data and I'm going to control shift enter. And now I can step through this, so we've obtained data as I go down here to the locals window, we brought in all the xy data and we're going to bring in x and y. So I can do this, a couple iterations, then we scroll down here. You notice that x is now equal to just the x values of our original data. y then is equal to just the y values of our original data. And we can keep going. We use the slope function and the intercept function in Excel to calculate m and b. And then finally we create the predicted values, based upon the model, we're creating the residual vector at the same time. So I'm just going to bump it out of here. Run to cursor, we've created all the residuals. And the last thing is to intake the residual of vector e and place that then on the spreadsheet where we type in the residuals function. All right, and when we do that, then we get the residuals on the spreadsheet. So this is how you could do kind of a more advanced application of array functions. This is now set up for any column of x and y data. So hopefully this helped, and I hope you enjoyed the Screencast. Thank you