For Cell B48, write a formula using the Sum function that will return the sum of the receipts for the decade of 1990s for grapefruit (5 points) For Cell B49, write a formula using two Sumif functions that will return the sum of the receipts for the decade of 1990s for grapefruit (5 points) Note that if you sum the years that are less than 2000 and subtract from that the years that are less than 1990, then you should have the sum of the 1990s For Cells B53 W53, write a single formula that can be copied to each of these cells that will calculate the sum of the receipts for each crop listed in B52 W52 without hard coding a number in the formula and the formula must use the information is Cell A53 (10 points) For Cells B57 B61, write a single formula that can be copied to each of these cells that will calculate the sum of the receipts for grapefruit for each decade without hard coding a number in the formula and the formula must use the information is Cells A57 A61 (10 points) Write a single formula that can use the information in cells A63 A67 that will sum up the receipts for each decade You should copy this formula for each decade that has data and for each crop Note that there should be no formulas copied to the row that has 2020s in it You may need to use the Sumifs function, the Value function, , and the Left function You should expect that your formula will use some portion of Cells A63 A67 and it will strip off the apostrophe s from the number put in these cells (15 points) In Cell B69, pose a question that can be answered by the data you just summarized and answer that question in B70 (5 points) Write a single formula that can use the information in cells A72 A76 that will average the receipts for each decade You should copy this formula for each decade that has data and for each crop Note that there should be no formulas copied to the row that has 2020s in it You may need to use the Averageifs function, the Value function, , and the Left function (10 points) In Cell B77, pose a question that can be answered by the data you just summarized and answer that question in B78 (5 points) Write a single formula that can use the information in cells A82 A86 that will average the receipts for each decade You should copy this formula for each decade that has data and for each crop Note that there should be no formulas copied to the row that has 2020s in it You must use the Sumif function and the Countifs function, and you may need to use the Value function, and the Left function This should confirm your results from (4) You are not allowed to use the Averageif function to calculate this result You should also note that you can find an average by taking the sum of the elements and dividing it by the number or count of the elements (15 points) Do not forget to account for the NAs in your calculations

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 15, 2024

For Cell B48, write a formula using the Sum function that will return the sum of the receipts for the decade of 1990s for grapefruit

For Cell B48, write a formula using the Sum function that will return the sum of the receipts for the decade of 1990s for grapefruit (5 points).
For Cell B49, write a formula using two Sumif functions that will return the sum of the receipts for the decade of 1990s for grapefruit (5 points). Note that if you sum the years that are less than 2000 and subtract from that the years that are less than 1990, then you should have the sum of the 1990s.
For Cells B53:W53, write a single formula that can be copied to each of these cells that will calculate the sum of the receipts for each crop listed in B52:W52 without hard coding a number in the formula and the formula must use the information is Cell A53 (10 points).
For Cells B57:B61, write a single formula that can be copied to each of these cells that will calculate the sum of the receipts for grapefruit for each decade without hard coding a number in the formula and the formula must use the information is Cells A57:A61 (10 points).
Write a single formula that can use the information in cells A63:A67 that will sum up the receipts for each decade. You should copy this formula for each decade that has data and for each crop. Note that there should be no formulas copied to the row that has 2020s in it. You may need to use the Sumifs function, the Value function, &, and the Left function. You should expect that your formula will use some portion of Cells A63:A67 and it will strip off the apostrophe s from the number put in these cells (15 points).
In Cell B69, pose a question that can be answered by the data you just summarized and answer that question in B70 (5 points).
Write a single formula that can use the information in cells A72:A76 that will average the receipts for each decade. You should copy this formula for each decade that has data and for each crop. Note that there should be no formulas copied to the row that has 2020s in it. You may need to use the Averageifs function, the Value function, &, and the Left function (10 points).
In Cell B77, pose a question that can be answered by the data you just summarized and answer that question in B78 (5 points).
Write a single formula that can use the information in cells A82:A86 that will average the receipts for each decade. You should copy this formula for each decade that has data and for each crop. Note that there should be no formulas copied to the row that has 2020s in it. You must use the Sumif function and the Countifs function, and you may need to use the Value function, and the Left function. This should confirm your results from (4). You are not allowed to use the Averageif function to calculate this result. You should also note that you can find an average by taking the sum of the elements and dividing it by the number or count of the elements (15 points). Do not forget to account for the NAs in your calculations.