26-Modelling Functions SUMPRODUCT

Summary

In this video, Alex helps a company creating software manuals model project costs and revenue in Excel. The company uses a project management technique involving three estimates—pessimistic, likely, and optimistic—and weights them to create a more accurate timeline. Alex demonstrates how to use the 'SUMPRODUCT' function to calculate a weighted average of task durations and how to narrow down data by filtering tasks that will take 100 hours or more. The video highlights Excel's powerful capabilities for project planning and data analysis, making complex calculations easier and more efficient.

Takeaways

• 😀 Alex is helping a company produce software manuals and is tasked with modeling the project cost and revenue in Excel.
• 😀 The company uses a classic project management technique to estimate task durations with pessimistic, likely, and optimistic estimates.
• 😀 The goal is to calculate an average estimate with weighted values, where the likely estimate has the highest weight (4), and pessimistic and optimistic estimates have a weight of 1.
• 😀 Instead of manually typing out a weighted average formula, Alex uses Excel's SUMPRODUCT function to multiply arrays and calculate the weighted average.
• 😀 The SUMPRODUCT function simplifies calculations by allowing multiple arrays to be multiplied together, with each individual result added up.
• 😀 Excel's SUMPRODUCT function requires arrays to have the same number of rows and columns, which is essential for the function to work properly.
• 😀 After entering the SUMPRODUCT formula, Alex also divides the sum by the total of the weightings to get the final weighted average estimate.
• 😀 SUMPRODUCT can also be used for conditional calculations. For example, it can count how many tasks are estimated to take 100 hours or more by using logical tests.
• 😀 In the second example, Alex demonstrates how SUMPRODUCT works with logical tests by checking if task durations are greater than or equal to 100 hours and returning TRUE or FALSE.
• 😀 The logical test results (TRUE/FALSE) are multiplied by 1, and then the ones are summed to find how many tasks meet the criteria (tasks taking 100+ hours).
• 😀 Alex emphasizes the usefulness of the 'Evaluate Formula' feature in Excel to break down and understand how complex formulas work, providing step-by-step insights into the calculation process.

Q & A

What is the main objective of the project in the script?

The main objective of the project is to help a company that produces software manuals estimate the time it will take to complete various tasks in a new manual production project using Excel.

What method does the company use to estimate task durations?

The company uses a project management technique that involves providing three estimates for each task: pessimistic, likely, and optimistic. These estimates are then weighted differently, with the 'likely' estimate given more weight.

How does the company weight the different estimates for each task?

The pessimistic and optimistic estimates each have a weight of 1, while the likely estimate has a weight of 4. This weighting allows the likely estimate to have a greater influence on the final calculation.

Why does the company use Excel's SUMPRODUCT function?

Excel's SUMPRODUCT function is used to simplify the calculation of the weighted average by multiplying arrays (estimates and weights) and then summing the results. It helps avoid clumsy manual formulas and makes the process more efficient.

How does the SUMPRODUCT function work in this context?

The SUMPRODUCT function multiplies each value in the estimates array by the corresponding value in the weights array and then sums the results. The formula is then divided by the total sum of the weights to obtain the weighted average.

What is the formula used for the weighted average calculation in the script?

The formula used is: =SUMPRODUCT(Estimates, Weights) / SUM(Weights), where 'Estimates' represents the task estimates and 'Weights' represents the assigned weightings.

What is the advantage of using absolute references for the weights in the formula?

Using absolute references for the weights ensures that they remain fixed when the formula is copied down to other rows. This prevents the weights from changing as the formula is applied to other tasks.

What problem does Alex address in the second part of the script using SUMPRODUCT?

In the second part of the script, Alex uses SUMPRODUCT to calculate how many tasks will take 100 hours or more. Instead of using COUNTIF, SUMPRODUCT is used to perform a logical test and return the count of tasks meeting the criterion.

How does SUMPRODUCT help in identifying tasks taking 100 hours or more?

SUMPRODUCT is used with a logical test to check if each task duration is greater than or equal to 100 hours. The TRUE/FALSE results are converted to 1s and 0s, and SUMPRODUCT sums up the 1s to give the total count of tasks meeting the threshold.

What is the purpose of the 'Evaluate Formula' feature in Excel?

The 'Evaluate Formula' feature helps users break down complex formulas step-by-step to understand how Excel calculates the result. It is useful for troubleshooting and ensuring the formula works as intended.