Practice Video: Modelling Functions: SUMPRODUCT

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate II

245 ratings

Macquarie University

Excel Skills for Business: Intermediate II

245 ratings

Curso 3 de 4 em Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

Na lição

Data Modelling

This module is all about data modelling. Learn to model different scenarios based on input, assumptions and/or outcomes. Also learn the use of functionalities like Goal Seek, Data Tables and the Scenario Manager to make your models more robust.

Practice Video: Modelling Functions: SUMPRODUCT5:50

Practice Video: Data Tables7:01

Practice Video: Goal Seek5:12

Practice Video: Scenario Manager8:50

Practice Video: Solver9:48

Week 5 Wrap-Up1:30

Conheça os instrutores

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

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.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2018 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play