Now that we're more familiar with array formulas,

let's have a look at the type of little problem

that an array formula can easily help us solve.

Coming back to our product list tab,

when Alex created this total,

he actually made a bit of a mistake.

He only included the first four values.

Obviously, as our users add additional values here,

we want them to be picked up in the calculation.

To change the array formula is really easy.

All we're going to do is change those 10's to 17's and then press control shift enter.

Formula updated, but not totally successfully.

We're now getting an NA error and that is

because we have some NA errors and we're trying to add them.

This is a problem that can occur in your workbooks if

an error does occur and it might be a completely legitimate error,

like you're trying to get average of values that haven't been completed yet,

your calculations will return errors as well.

What we can do is we can use an array formula to actually ignore cells that have arisen.

That's what we're going to do now.

We're going to come back to this calculation and just after the sum

we're going to slip in and if NA over here and press tab.

We're going to check if that is returning an error,

then we're going to replace that with a zero instead,

and then don't forget to put your bracket on the end.

And then of course control, shift,

enter and there is our correct result without the error problem.

Of course you could apply this in a lot of different situations.

Now let's look at another example.

This time we actually want to compare two data sets and identify the differences.

To be more specific,

the client would like to know where the prices have changed.

So they'd like to know how many of the US dollars prices have

increased since the old list and how many have decreased.

Once again, we're going to use array formulas to solve this.

We're going to compare the two ranges and add up

all the situations where the new price is greater than the old price.

We're going to start with our equal sum and then we're going to compare our two ranges.

I'm going to open up another set of brackets and I'm going to ask if

my US dollar price is greater than my old US dollar price.

Now that little question there because I'm comparing two ranges it's

going to return another array and that array is going to contain true's and false's.

True if the US dollar price has increased and false if it hasn't.

Now, I can't actually just add up true's and false's.

So what I'm going to do is multiply that array by one.

This is a little trick.

What that will do is wherever I get a false multiplied by one, that would give me zero.

A true multiplied by one will give me a true.

So let's close my bracket and press control, shift,

enter and there we could see three other prices have increased.

Now that was a little trickier.

So let's just do another example so you get familiar with it.

Same thing, but now we're looking for the decreases.

Again, we start with our sum and open another bracket.

This time we're going to check if our US dollar price is less than

the old price to see if it's actually decreased.

So US dollar price and we're going to check if it's

less than old US dollar price. Close your brackets.

Again, times by one.

Close your second bracket and control, shift, enter.

As you can now see that two other prices have decreased.

So there are a couple of little examples to get you more familiar

with the types of problems you can solve using array functions and formulas.

Make sure you try this week's practice challenge which is going to give you

heaps of other ways of working with array formulas.