In this workout we're asked to calculate total sales, where asked to calculate it first using the intermediate step i.e., the total sales column, and then calculate it directly from the inputs using sumproducts, avoiding the intermediate step. So let's go down to our total sales column here. To calculate this just for the first row here, I'll have to take the unit sold and multiply that by the price per unit.

And you can see my formula being revealed on the right hand side.

I now need to copy that down. I'm going to click on the fill handle at the bottom right hand corner, click and drag that down. I've now got the total sales for each of these, and then underneath that, I can sum that upwards and my total sales is 1,741. But now let's try and do that using sumproduct. If I start typing in the sumproduct formula, it gives me this white box underneath, and that's called the syntax. It asks me, firstly, for array 1. The first thing that I want to multiply, I'm going to select all of the units.

I then type a comma, and you may have to type a semicolon, and it then asks me for array 2, and I'm going to select all of the price per unit.

I then close the brackets and what Excel will do, it will multiply the 15 by the 10 and then multiply the 39 by the 11 and do that for all of them, and then sum them up. And what we get is exactly the same answer as we had above.

Let's do a second example. In this second workout, it says, calculate the expected value of sales units for each year. Calculate it first using the intermediate step i.e. weighted values, and then calculate it directly from the inputs using sumproducts, avoiding the intermediate step.

So what we've got below are three scenarios. We've got the probability of the low scenario happening 30%, and then 60% for the medium and 10% for the high. Then we've got the sales units. In each of those scenarios, the sales units in the low scenarios, 4,000, medium 5,000 and high 8,000.

In order to calculate the expected value, we'll do each of the weighted values and then sum them up.

So for our low weighted value, I'll multiply the 30% by the 4,000, and if I now copy that down, so I select the cells I want to copy into, and I then press control D. But I could of course have just used the fill handle, and then I copy it to the right. With control R, I can now sum up each of the three numbers in a column to get the expected value. So in year one, we would expect a value of 5,000 sales units.

I can then copy that to the right, and I've got an expected value for each year.

But was I able to do this without doing these weighted values? Some products allows us to do that.

The syntax, that little white box underneath is asking me for array 1. So I'll go up to the probabilities and I'll select the three probabilities.

Then I press a comma and I select the three sales units.

What some products will now do is multiply the 30% probability by the 4,000 low units, then the 60% probability by the 5,000, et cetera. It will then sum them up and it should give me exactly the same number as the expected value that we calculated earlier. It should give me the 5,000 fingers crossed. It gives me exactly that, and I can now copy that to the right. And all of them are the same. So we could have skipped out that weighted value step by just using sumproduct.