Alex is now going to complete the product list sheet,

which allows the client to give customers quick quotes.

He is going to use array functions to complete these tasks.

Now array functions are quite an advanced feature here in

Excel and they work slightly differently, to conventional functions.

They are sometimes called CSE formulas,

because you have to press Control Shift Enter,

when you finish typing them in.

Let's have a look at an example of an array formula.

What we need to do is get the Australian dollar price for each of the product list items.

So we're going to need to take the U.S. dollar price and

multiply it by the Australian dollar rate.

What we would normally do is create a single calculation and then copy it down.

But what Alex is going to do is create

a single calculation that gives him multiple answers.

This is what's called a multi-cell array formula.

Let's see how it's done.

So we start by selecting all the cells,

where we want the answers to go.

We then, typed in our equals without clicking back into the workbook,

because then we'll de-select.

Now, we select the first set of values we want to multiply,

which is our US dollar prices.

So we're going to select the whole array,

and you can see why they called array formulas,

because an array is a whole selection of values.

Having done that, we're going to type our

times and we're going to click on Australian dollar rate.

Now, if I just press enter,

I'm going to get a value error,

because this is not valid syntax for conventional formulas.

If I press Control Shift Enter,

however, it works very well.

So we have now used a single calculation to return multiple results.

Now if you have a quick look in the formula bar,

you'll see the array formula is slightly different.

Array formulas are always shown in curly braces.

They also behave slightly differently.

So if I were to come and select one of these values and try and delete it,

Excel tells me, "You cannot delete part of an array formula", which makes sense.

All those values are being calculated by a single formula,

so I can't change one of them on its own.

That's not to say I can't update all of them.

So if I select the whole lot,

I can now go and change the formula or if I wanted to I could delete it.

So you can edit them,

but it has to be all or nothing.

Some people see this as a benefit because it does prevent

accidental changing of part of an array.

We have a look at a multi-cell array formula,

but you also get single cell array formulas,

and these are possibly more useful and more common.

For example, I now want to calculate my Australian dollar total for this quick quote.

What I need to do is multiply each of

the quantities by the price and then add them all together.

We can do this using an array formula.

Let's see how it works.

So I'm going to click into F4 and type =SUM.

Now what I need to do is multiply this array by this array and SUM the contents.

To do this, I select my first array,

type my multiply, select my second array,

and then Control Shift Enter.

And I now have my total,

without having needed to calculate a subtotal column.

So we start to see how powerful these array formulas can be.

You can also use something called an array constant.

This is an array of values that you specify they are constant,

rather than referring to cell references.

Array constants are specified by putting the values

in curly braces and separating them with semi-colons.

So to look at an example,

we now want to get our three highest Australian dollar prices.

So we're going to use our LARGE function.

I'm going to start by selecting all three,

because I'm doing another multi-cell array formula here,

and I'm going to type equals LA,

for large and press tab.

I'm going to select all of my Australian dollar prices,

and then I'm going to type a comma.

Our k is the constant array one, two, three.

But note, we're going vertically,

so we must remember to use our semi-colon.

So I open up my curly braces,

type a one, semi-colon, two, semi-colon, three.

Close the curly braces and then press Control Shift Enter.

And there are my three highest prices.

So that was a quick introduction to array formulas.

In the next two videos,

we'll start looking at how we can use them to solve

more interesting and complex problems.