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.
Dr Yvonne BreyerDirector, Learning and Teaching
