Question

SUMPRODUCT Quiz xlsm - Excel View Chemolice15 ACROBAT bile HomeInsert Page Layout formulasDate Review Team Elle - Open View Topic and Watch Associated Topic Que side by side Associated Video Switch Between View instructions Open Files in Window Grade Save Quiz and close Quiz Record Score This Quiz 810 Excercise: A trucking company has bases in the cities listed. The data shown are averages for a troical month. Find the total monthly costs for all cities for all Eastern cities, and for all Westem cities with appropriate formulas. These formules should react correctly even some cries are related Eastern Westmonth How dow oroducts calculate when one of the c h aos and is • All cities 10 Eastern cities 11 All Western cities Western city Miles per trip Trips per month Dollars per talon S126 5141 S16 $327 5349 $ $165 13 City 14 Boston is New York 16 oc 17 Charlotte 18 Philadelphia 19 Pittsburgh 20 Cleveland 21 St Louis 22 Kansas City 23 Dallas 24 Houston 25 Atlanta 26. Mimi 27 Los Angeles 28 San Diego 29 San Francisco 30 Seattle 31 Phoenix Gallons per mile 0.0435 0.0417 0.0313 0.0303 0.0313 0.0435 0.038 0.0455 0.0500 0.0333 0.0357 0.0323 0.0100 0.0185 00100 0.0175 00320 00500 0.0345 127 SU 5151 53.27 $3.32 $3.34 $341 122 101 51 Quiz DOLL

I need help with the Excel formulas for All Eastern cities and All Western cities using SUMPRODUCT.

Answer 1

We find Total monthly cost for All cities using Excel function to the follow ing data Total Month ly cost for All cities = SUMPRODUCT(D 14:D 32,E 14:E 32,F14:F 32,6 14:G 32) = $ 35,128.09 MITY HICIIL ULUMCI зурса LUILTY =SUMPRODUCT(D14:D32, E14:E32,F14:F32, G14:G32) G H I J K L All Cities =SUMPRODUCT(D14:D32, E14:E32,F14:F32, G14:G32) Eastern City 13 City 14 Boston 15 Newwork 16 DC 17 Charlotte 18 Philadelphia 19 Pittsburgh 20 Cleverland 21 St.Louls 22 Kansas City 23 Dallas 24 Houston 25 Atlanta 26 Miami 27 Los Angels 28 San Diego 29 San Fransico 30 Seattle 31 Phoenix 32 Denver Western city Gallons per mile Miles per Trip Trips per Month Dollars per gallon 0.0435 20 $3.26 0.0417 139 $3.43 0.0313 197 $3.63 0.0303 171 $3.27 0.0313 106 $3.49 0.0435 128 $3.39 0.0385 125 $3.65 0.0455 177 $3.44 0.05 178 $3.50 0.0333 117 $3.51 0.0357 169 $3.27 0.0323 190 $3.32 0.04 162 $3.34 0.0385 181 $3.41 0.04 119 $3.60 0.0476 166 $3.63 0.037 188 $3.29 0.05 1 149 $3.30 0.0345 1 195 $3.49

We find the Total Monthly cost for Eastern Cities using Excel function Eastern Cities : =SUMPRODUCT(D 14:D 20.E 14:E 20.F14:F20.614:G20)+SUM PRODUCT(D25:D 26.E 25:E 26.F 25:F26,625:626) $13.967.87 fx =SUMPRODUCT(D14:D20,E14:E20,F14:F20,614:G20)+SUMPRODUCT(D25:D26, E25:E26,F25:F26,G25:626) MN 00 All Cities $35,128.09 10 Eastern Citites -SUMPRODUCT(D14:D20,E14:E20,F14:F20,614:G20)+SUMPRODUCT(D25:D26,E25:E26,F25:F26,625:626) 12 Eastern City 79 70 1 70 79 13 City 14 Boston 15 Newwork 16 DC 17 Charlotte 18 Philadelphia 19 Pittsburgh 20 Cleverland 21 St.Louls 22 Kansas City 23 Dallas 24 Houston 25 Atlanta 26 Miami 27 Los Angels 28 San Diego 29 San Fransico 30 Seattle 31 Phoenix 32 Denver Western city Gallons per mile Miles per Trip Trips per Month Dollars per gallon 0.0435 T 120 T 50 $3.26 0.0417 139 $3.43 0.0313 197 120 $3.63 0.0303 171 $3.27 0.0313 106 $3.49 0.0435 128 | $3.39 0.0385 125 l $3.65 0.0455 177 $3.44 0.05 178 $3.50 0 0.0333 127 $3.51 0.0357 169 128 $3.27 0.03231 190 T 122 $3.32 0.04 162 $3.34 1 0.0385 181 138 $3.41 0.04 119 101 $3.60 0.0476 166 $3.63 0.037 188 79 $3.29 0.05 149 59 $3.30 0.0345 195 $3.49 117 1 1 96 75

We find the Total Monthly cost for Western Cities using Excel function Western Cities : =SU M PRODUCT(D 27:D 32,E 27:E 32,F27:F32,G 27:6 32) = $ 12,553.43] E F - All Cities $35,128.09 10 Eastern Citites $13,967.87 11 Western Citites =SUMPRODUCT(D27:032,E27:E32,F27:F32,627:432) 12 Eastern City 1 1 OOOO | 1 | 68 0 13 City 14 Boston 15 Newwork 16 DC 17 Charlotte 18 Philadelphia 19 Pittsburgh 20 Cleverland 21 St.Louis 22 Kansas City 23 Dallas 24 Houston 25 Atlanta 26 Miami 27 Los Angels 28 San Diego 29 San Fransico 30 Seattle 31 Phoenix 32 Denver Western city Gallons per mile Miles per Trip Trips per Month Dollars per gallon 0.0435 120 50 $3.26 0 0.0417 139 79 $3.43 0.0313 1 97 120 $3.63 0.0303 1 71 70 $3.27 0.0313 106 50 $3.49 0.0435 128 69 $3.39 0.0385 125 70 $3.65 0.0455 177 $3.44 0.05 178 79 $3.50 0.0333 1 17 127 $3.51 0.0357 169 128 $3.27 0 0.0323 1 90 122 0.04 162 96 $3.34 1 0.0385T 181 138 $3.41 0.04 119 101 $3.60 1 0.0476 166 88 $3.63 0.037 188 79 $3.29 1 0. 05 1 49 59 $3.30 1 0.0345 T 195 75 $3.49 0 1 $3.32 0 0 0 0 33 A 11 answers are E F 9 All Cities 10 Eastern Citites 11 Western Citites $35,128.09 $13,967.87 $12,553.43 Eastern City Western city 1 13 City 14 Boston 15 Newwork 16 Dt 17 Charlotte 18 Philadelphia 19 Pittsburgh 20 Cleverland 21 St.Louls 22 Kansas City 23 Dallas 24 Houston 25 Atlanta 26 Miami 27 Los Angels 28 San Diego 29 San Fransico 30 Seattle 31 Phoenix 32 Denver Gallons per mile Miles per Trip Trips per Month Dollars per gallon 0.0435 120 $3.26 0.0417 139 $3.43 0.0313 197 120 $3.63 0.0303 171 $3.27 0.0313 106 $3.49 0.0435 128 $3.39 0.0385 125 $3.65 0.0455 177 $3.44 0.05 178 $3.50 0.0333 117 $3.51 0.0357 169 $3.27 0.0323 190 $3.32 0.04 162 96 $3.34 0.0385 181 $3.41 0.04 119 101 $3.60 0.0476 166 $3.63 0.037 188 $3.29 0.05 149 $3.30 0.0345 195 $3.49 1 122 138