Use SUMPRODUCT to calculate effort based on weighted risk probability

**dammyshaggs** · 09-17-2018, 05:21 PM

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!!

Weighting	                         0.01	                 0.04	                 0.1	                0.25	               0.6	
High Level Tasks	Natural Disaster	Stormy Weather	Bad Weather	Good Weather	Great Weather	Estimated Hours
Driveway					                                                                                         20	
Backyard				                                                                                        	30	
Front yard					                                                                                         25	
Back veranda					                                                                                 25	
Front porch					                                                                                  20	
						
TOTAL HOURS						                                                                                                   0.0

**joeu2004** · 09-17-2018, 07:01 PM

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.

**joeu2004** · 09-17-2018, 08:20 PM

In mrexcel.com/forum/excel-questions, you wrote:

``Lemme explain better what i did which i believe is giving me wrong answers.I used fill handle to do the increasing factor for each column.Then use the all data to perform SUMPRODUCT for the estimated hours. But that approached fails!``

Perhaps you merely need some guidance in using mixed relative/absolute references of the form A$1 and $A1.

It would behoove you to attach an Excel file that demonstrates your values and formulas in your table.

Sadly, that's not as easy as it should be. The "Attachments" botton in the toolbar after you click "Go Advanced" does not work. Instead, scroll down and click "Manage Attachments".