Working with array formulas more easily

- [Narrator] If you work with Excel array formulas you've gotten used to the idea of completing the formula or the function by pressing control, shift, enter. But Excel is changing the way these kinds of formulas work. Some of you maybe have not ever used the formulas at all, so in this case it will just be something easier that you might not have been alerted to. In this particular work sheet here we've got totals here in column d based on the information to the left, and a grand total at the bottom. In some scenarios let's say we don't need these interim totals at all we just want the grand total.

What if we could somehow take all of these times all of these and add them up. If we were writing a formula we might try the following: sum, let's add up all of these times all of these. Now in older versions of Excel if you press enter right now you'll get the value pop up error message. Looking like what we're seeing here down in row 22 just for reference. But if you have the latest Office 365 upgrade working with Excel, simply pressing enter does give you a correct answer, it's the same number we see right here, which is the total of all those.

If you have the older versions of Excel and not the most recent updates if you press enter here you will get that value error, but if you press control, shift, enter you will get an appropriate answer. And look in the formula bar at the top of the screen we see this embedded until we start to edit. We see that embedded in braces. That's no longer necessary. Once again if we're writing this formula with the latest upgrades we can type it in like this, enter and we have a correct answer. As we click on this we don't see those braces up there in the formula either.

And another simple little example over here. Here too we've got an average of those differences between order and shipping. Let's just cut to the chase, let's say again, the scenario is such we don't need these interim totals here why not simply average. And the way these calculations were done was simply the shipping date minus the order date. Let's take all the shipping dates, minus the order date. And once again, like in the previous example, we can now with the latest upgrades simply press enter and we will have a correct answer.

That's the same answer we're getting down here, which is the average of those differences. And that's all we're doing here. And when it pertains to functions as well, you might be familiar with frequency function? Here too, this is, well it's simply an array formula, with an array function equal frequency. We want to refer to all the data in column b from here down to the bottom. I'll simply click here and I do know that it goes down to row 621, so simply highlight this and complete the text. Although, I could use a column reference here.

It's better to put in the actual address so we get no extraneous results. Comma, if you're familiar with the frequency function you've set up a bins array, doesn't have to be adjacent to it, but often it would be. Doesn't have to be equally spaced, but often it would be. This is the various ranges of salaries and the chart to the right, which for the moment looks empty, is key to this. Here too, in older versions of Excel you would now press control shift enter, and you would've needed to highlight all those cells furthermore from E2 down to E11. Here just one we'll press enter and we've got answers everywhere.

The newer array form is don't need the braces. You don't need to press control shift enter. And here's one more. Two different functions. The trend function will create a straight line here. It's analogous to what we might do by right clicking adding a trend line. That puts in a linear trend line. If you want actual numbers associated with that point we can and sell c2 simply type equal trend. Left praenthesis, highlight these cells here down to the bottom and simply press enter.

And we've got a list there. And the chart picks up up and projects that for us. And the same thing with growth which we'll do almost the same thing but with an exponential line. Equal growth. So we'll simply refer to these cells over in column b again and enter. And we've got that entry as well too. So if you're new to array formulas it's going to be a lot easier than you would have imagined and if you have been working with them I think you'll recognize, this is a better way to work with these kinds of formulas and functions.