0:03

This week Alex is going to be helping a company that produces software manuals.

Â The company is embarking on a new project to produce a range of new manuals.

Â And what they're looking to do is model the project cost and revenue in Excel.

Â Excel has a fantastic selection of

Â modeling tools and that's what we're going to be looking at this week.

Â The first thing Alex needs to help with is working

Â out how long each task is going to take in the project.

Â For each training manual,

Â they've broken down the task into several high level tasks.

Â What they want to do is get an estimate of how long each of those tasks is going to take.

Â They're using a classic project management technique where they've got a pessimistic estimate,

Â a likely estimate and an optimistic estimate.

Â And what they want to do is get an average of these three to get their actual estimate.

Â There is one catch however.

Â They actually want to add more weighting to the most likely estimate.

Â Here the proposal is that the pessimistic only has a weighting of one.

Â The optimistic a weighting of one but the likely a weighting of four.

Â And it's possible they may want to play around with these weightings.

Â So these may be subject to change.

Â So what we want to do here is basically say (64 X

Â 1) + (36 X 4) + (32 X 1) then divide by six.

Â Typing in that formula however is going to be kind of clumsy.

Â So Excel gives us a really useful function to solving exactly this kind of problem.

Â And this is called SUMPRODUCT.

Â SUMPRODUCT allows us to multiply one array by

Â another array and add up each individual results.

Â Let's see how it works.

Â We are going to type = sum and select SUMPRODUCT.

Â You will notice in the brackets,

Â Excel is asking us to enter a minimum of one array but we can add more than that.

Â Something to note is that all the arrays must have the same number of rows and columns.

Â So we're going to select our first array which is our estimates for

Â the high level design and then we're going to type

Â a comma and we're going to put in our second array which is our weightings.

Â But of course we want that to remain fixed.

Â So we're just going to make that absolute and close our brackets.

Â And we actually want to get the average.

Â So we will divide the sum by the sum of the weightings.

Â Again, make that absolute.

Â Then we press enter and that is weighted average. Copy that down.

Â We now have a nice little estimate of how long it's going to take to produce each manual.

Â SUMPRODUCT can actually be used to do something else quite interesting as well.

Â It can be used to narrow down your data and add it up where a certain criterion is met.

Â So, in the next example what we want to do is find out

Â how many of these tasks are going to take 100 hours or more to complete.

Â But instead of using a COUNTIFS for example we're going to use the SUMPRODUCT.

Â We're going to click into G6 and type = SUMPRODUCT.

Â This time we're going to do something quite odd.

Â We're going to check each of these values to see if they're greater than or equal to 100.

Â And each result is going to return a true or false because we're going to do

Â a logical operation and then we're going to take those results and multiply them by one.

Â And if it return false we'll get zero times one which is zero.

Â If it's returned to true we'll get one times one which is one

Â and then we're going to add up all the ones to get our final results.

Â Let's see how it works.

Â So the first thing I'm going to do is say one times and then open another bracket.

Â Here I'm going to do my logical test

Â where I'm going to check that entire array and see if it's

Â greater than or equal to 100 close my brackets and close my bracket for my SUMPRODUCT.

Â When we click enter,

Â we're expecting to get three and sure enough we did.

Â Now, that result may be a little surprising.

Â If you want to work through it a bit more slowly.

Â Don't forget what we've learned about last week which is how we evaluate formula.

Â And this is great for this kind of tricky thing.

Â If I click evaluate formula and click on evaluate to

Â see the next step you see what it's done is it solved

Â that first array and we've got a whole bunch of Trues and Falses and

Â we're going to multiply each of those by one and then add up each of those results.

Â So when I click evaluate, there we go.

Â We've got three ones,

Â three zeros and then we click evaluate again and it adds up to three.

Â So that is how we can use SUMPRODUCT to solve some slightly more tricky problems.

Â Why don't you have a go.

Â