Ag Bus 260
Midterm 2
100 Points
2/18/2014
Dr. Hurley
Instructions: Locate your test folder which can be found at:
\\cp-file-cafes\cafes$\OpenToAll\AGB260-Hurley. Before you open the file, you should rename the file to be your last name-first name.XLSX, e.g., Hurley-Sean.XLSX. Inside this folder you will find an Excel spreadsheet that has two Tabs which read: Tomato Data Set and Cattle Data Set. Each one of these relates to the questions below. Follow the instructions given below. The questions for A have been abbreviated in some cases and added to the spreadsheetfor your convenience. When you are finished with the exam, save it to your folder on the O drive and then email me a copy.
Question A (Total Points: 60):These set of questions relate to the tomato data set.
A1. Given the dates in column D, create a formula in cell B818 that will tell you a number designation for the day of the week that the data was first collected. In cell B819, tell what the number in B818 is designating. Create a formula in cell B820 that will tell you a number designation for the day of the week that the data was last collected. In cell B821, tell what the number in B820 is designating(5 points).
A2. In cell B24, create a formula that tells how many rows were left blank in the Market Tone Column, column I(5 Points).
A3. Create a formula in cell B827 that tells how many rows in the Environment Column, column G, have the response Adapted Environment in it. Note that a hint has been left in the merged cell for A824:C824(5 Points).
A4. In cells B831:B846, write a formula/formulas that will tell you how many times each response in cells A831:A846 shows up in the Supply Tone Column, column H (10 Points).
A5. In cells B849:B857, write a formula/formulas that will tell you the Average High Price that is associated with the Item Size Column, column F, for each of the responses in cells A849:A857. Make sure that you round your values to two decimal places without using formatting. You should find that the average high price for xlge is 11.23(10 Points).
A6. In cell B861, create a formula that tells you what the average High Price for tomatoes was in October. Make sure that you round your values to two decimal places without using formatting. Hint: do not forget the beginning and ending days in October(10 Points).
A7. Suppose that each layer in a two layer carton has six tomatoes each. Write a function in Cell B864 that takes the response in cell A864 and strips out the number and multiplies it by 6 to get the total number of tomatoes in the particular package (5 Points).
A8. Write a nested If function for cells B869:B871 that will take the responses in cells A869:A871and turns the item size: xlge into Extra Large, lge into Large, med into Medium, and jbo into Jumbo.
Question B (Total Points: 40): This set of questions relate to the Cattle Data Set.
B1. In cells A19:A102, create a single formula that will place the numbers 1 through 6 in repeating order as seen in Figure 1 below (5 points).
B2. In cells B19:B102, create a single formula that uses the information in cells G19:H24 that will take the numbers in cells A19:A109 and put the bimonthly period that corresponds to the number(5 points).
B3. In cell C19, put the year 2000. In cells C20:C102, write a formula that puts the year in for the corresponding bimonthly period. Each time you come to the January/February time period, you need to have the formula increment up by one year in the corresponding cell (see Figure1)(5 Points).
B4: In cells D19:D102, write a formula that brings together the information in the B column with the C column that is separated by a colon and a space (see Figure1) (5 Points).
B5: In cells E19:E102, write a single formula that will give you the average prices for each bimonthly period, i.e., every two months, taken from cells A2:M16 (see Figure1)(20 points).
Figure 1:Example of Final Output for Question B.