Use SUMPRODUCT to calculate effort based on weighted risk probability

Been having hard time solving this question.I got it wrong and i believe the workings used is the cause of it. I need clearer explanation.


Question 1
The following file describes the situation faced by a landscaping company, 'Landscaping Solutions'.
If there is great weather, the company takes the following hours to complete a driveway, backyard, front yard, back verandah, and front porch: 20, 30, 25, 25, and 20 hours respectively.
Good weather increases all times by a factor of 1.1.
Bad weather increases all times by a factor of 1.25.
Stormy weather increases all times by a factor of 1.5.
A natural disaster increases all times by a factor of 2.0
Great weather, good weather, bad weather, stormy weather, and a natural disaster have the following probabilities of occurrence: 0.6, 0.25, 0.1, 0.04, and 0.01 respectively.
All these probabilities (weightings) sum to 1.
Use SUMPRODUCT to calculate the estimated hours of work to complete the landscaping.
Please answer using the format ###.##










Thanks!!

Please Login or Register  to view this content.

Since this is homework or an exam question about SUMPRODUCT, it would be inappropriate to provide a turnkey solution using SUMPRODUCT.

But let's be sure you understand the mathematical principle (probability theory), which might be your stumbling block.

First, the problem is better stated as: calculate the expected (average) hours to complete the task, based on the given "conditional probabilities".

Consider the 20 hours for the driveway. If we knew the weather would be "great", the estimated time would be 20. (Let's write 20*1. You'll understand why later.) But we don't know that. Instead, we know the probability is 0.6 (60%). So the "expected" time for "great" weather is 20*1*0.6.

Likewise, the "expected" time for good, bad, stormy and disaster weather is 20*1.1*0.25, 20*1.25*0.1, 20*1.5*0.04 and 20*2*0.01 respectively.

Thus, the total "expected" (average) time for any weather is the sum of those terms, which can be written
20*(1*0.6 + 1.1*0.25 + 1.25*0.1 + 1.5*0.04 + 2*0.01). The term between the parentheses should be reminiscent of a SUMPRODUCT calculation.

Repeat that kind of calculation for each of the landscape areas, and sum them all for the total.

It is unclear to me whether the assignment wants you to calculate the total in a single SUMPRODUCT expression, or as the sum of the individual SUMPRODUCT expressions.

It would certainly be the latter (easier) if the table at the end of your posting is part of the assignment specification, not your interpretation of it.

Cross-posted at: https://www.mrexcel.com/forum/excel-...umproduct.html
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html