Due Date Will Be Given in Lecture

Ag Bus 260

Activity 3

70 Points

Due date will be given in lecture

Dr. Hurley

Directions:The purpose of this activity is to strengthen your familiarity with Excel in the areas of using advanced functions. All the data you need to finish the activity is located in the Activity3Data.xlsx workbook. All work should be done in Excel and submitted to me by email no later than 5:00. If the question asks you to do something in a particular way, you must do it the way it is asked to get credit for the problem.

Question A(70 Points Total): Notice in the Question A worksheet that you have been given a set of data regarding US cash receipts for different fruit and tree nut crops for the US that spans from 1980 to 2012. You are tasked with examining these receipts for each crop by decade. To accomplish this task you need to do the following:

1)Write a single formula that can use the information in cells A40 through A44 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 2020’s in it. You will 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 A40 through A44 and it will strip off the apostrophe s from the number put in these cells(15 points).

2)In a textbox, pose a question that can be answered by the data you just summarized (5 points).

3)Write a single formula that can use the information in cells A46 through A50 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 2020’s in it. You will need to use the Sumif function (maybe twice), the Value function, &, and the Left function. This should confirm your results from (1)(15 points).

4)Write a single formula that can use the information in cells A52 through A56 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 2020’s in it. You will need to use the Averageifs function, the Value function, &, and the Left function (15 points).

5)In a textbox, pose a question that can be answered by the data you just summarized (5 points).

6)Write a single formula that can use the information in cells A58 through A62 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 2020’s in it. You will need to use the Sumif function, the Countif function, the Value function, and the Left function. This should confirm your results from (4)(15 points).