Instructions: Locate Your Test Folder Which Can Be Found At: L: Shared AGB 260-03. Before

Ag Bus 260

Midterm

260 Points

2/15/2017

Dr. Hurley

Instructions: Locate your test folder which can be found at: L:\Shared\AGB 260-03. Before you open the file, you need to rename the file to be your First name-Last name, e.g., Sean-Hurley. If you do not name the file correctly, you will lose 5 Points. Inside this folder you will find an Excel spreadsheet that has 3 Tabs which read: Question A, Question B, and Question C. Each one of these relates to the questions below. Follow the instructions given below. Examples have been provided for a couple of the questions to give you an idea what type of answer I am looking for, but in no way are they necessarily the full answer. When you are finished with the exam save it to your folder on the L drive and then email me a copy. If you are unable to send it by email. Save it to your One Drive and share the file with me. If you happen to be color blind, please indicate so in a textbox on the first tab.

Question A (Total Points: 130): On the Question A Tab is data related to Fall enrollment numbers for Agribusiness and Agricultural Economics programs in the United States. This data was taken from the Food and Agricultural Education Information System managed by Virginia Tech University. Below you will have a set of tasks to complete utilizing Excel.

1)In Cells E1:P1, merge the cells and center the title Student Totals. Make sure the Font is 16 Point Times New Roman. The fill for the cells should be a green color.(5 points).

2)In Cells A1:P1, make sure that each text is underlined and has a 45 degree alignment. The fill color should be Grey. This row should be high enough to show all the text(5 points).

3)In Cells A3:A110, provide the ranking of each school based on the number of undergraduates they had in 2015. These ranking should be descending and you should average when ties occur. You should find Texas A&M is number 1(10 points).

4)In Cells D3:D110, provide the Institution Type based on the Institution Code that is provided in cells A114:B116. For example, Alabama Agricultural and Mechanical University is an 1890 Land Grant institution(10 points).

5)In Cells Q3:Q110, put the first letter of the name of the corresponding institution in column B. For example, Alabama Agricultural and Mechanical University starts with the letter A(10 points).

6)In Cells C114:C116, provide the total number of institutions that belong to the institution type represented in the corresponding cells B114:B116(10 points).

7)In Cells D114:D116, provide the total number of students for 2015 that are at each institution type represented in the corresponding cells B114:B116 (10 points).

8)In Cells D114:D116, provide the average number of students for 2015 that are at each institution type represented in the corresponding cells B114:B116. Make sure that the numbers are rounded to three decimal places and have four decimal places showing, i.e., you need to make sure the number is truly rounded not formatted to look like it was rounded. For instance, you should find that Cell E116 has 169.2920 in it.(20 points).

9)In Cells B122:D139, sum up the number of students that attend a university that’s name starts with the letter in the corresponding cells A122:A139 and attended the particular institution type given in Cells B121:D121. An example of part of the answer is given in Figure 1 below(20 points).

Figure 1: Example of the Answer for question A9

10)In Cell B141, find the difference between the sum of Cells B122:D139 and the total number of students that were at all three types of institutions in 2014. (5 points).

11)In Cells B122:D139, sum up the number of students that attend a university that’s name starts with the letter in the corresponding cells A122:A139, attended the particular institution type given in Cells B147:D164, and was ranked at or below (i.e., smaller numbers) than the number in Cell B144. An example of part of the answer is given in Figure 2 below (20 points).

Figure2: Example of the Answer for question A11

12)In Cell B166, find the sum of Cells B147:D164(5 points).

Question B (Total Points: 50):The next set of questions pertain to the Question B tab.

1)In Cells A2:A102, put the numbers -50, -49, -48, …, -1, 0, 1, …, 48, 49, 50, i.e., starting in cell A2 put -50 and increment up by 1 until you get to 50 in Column A. Copy Cells A2:A102 and transpose and paste the values into Cells B1:CX1(5 points).

2)Make Rows 1 through 102 a height of 30 points(5 points).

3)In cell B2, write a formula that will square the number in column A and add that to the squared value from the number in the first row, i.e. it should give you X2 + Y2 where X represents the corresponding numbers in the A column and Y represents the corresponding numbers in the first row. This formula should be copied into Cells B2:CX102. You should get 5,000 for B2, 4901 for C2, 4804 for D2, etc. (5 points).

4)For Cells B2:CX102, format all the cells that are between 300 and 700 to have a purple fill(5 points).

5)For Cells B2:CX102, format all the cells that are the bottom 10% to have a fill color that is flesh tone, i.e., yellowish-orange(5 points).

6)For Cells B2:CX102, format all the cells that are 0 to have yellow fill color(5 points).

7)For Cells B52:CX102, format all the cells that are between 20 and 30 to have a blue fill color(5 points).

8)Using conditional formatting make the cells that are between the rows 40 and 45 and simultaneously between the columns 55 and 60 a green fill color. Using conditional formatting make the cells that are between the rows 40 and 45 and simultaneously between the columns 44 and 49 a green fill color (5 points).

9)In Cell B108, put the value that is in the cell named YellowCell(5 points).

10)In Cell B110, briefly (2-3 words) describe what you see when you zoom to 10% (5 points).

Question C(Total Points: 80)The next section pertains to the Question C tab. These questions pertain to the monthly average cattle prices in cells B4:M74. If you need to add a column to accomplish any task, you will receive only half credit for the question you used the extra column on. Figures 3 and 4 below give you examples of the answers

1)Write a single formula that will average the beef prices for each month between the dates given in A77:A83. Copy this formula to Cells B77:M83. An example of the answer is given in Figure 3 below(20 Points).

Figure3: Example of the Answer for question B1

2)Using the Hlookup function and the cells B2:M3, put the months starting with January into Cells Q4:Q855(10 Points).

3)In Cells R4:R855, write a function that will start with the Year 1944 and put the corresponding year for the month. Each time you get to January, you should increment up a year. You will lose 5 points if you have to put the value 1944 into Cell R4 that is not part of a formula (10 Points).

4)In Cells R4:R855, put the corresponding month followed by a 15 and a comma and then the corresponding year from the information found in Cells Q4:R855, e.g., January 15, 1944. If you do not use a formula to do this problem, you will get five points deducted(10 Points).

5)Write a formula that puts the corresponding cattle price from Cells B4:M74 in Cells S4:S855. You may want to use the Row Behavior Column in orange and the Column behavior column in green to try to figure out the behavior you will need in your function to achieve the goal. If you actually use these columns in the function, you will lose 10 points for each column used(30 Points).

Figure 4: Example Answer for QuestionsC2-C5