AGB 260: Agribusiness Information Technology

1. AGB 260: Agribusiness Information Technology Importing Data, Managing and Transforming Data, Data Validation, and Handling Errors

2. Useful Chapters in the Textbook Regarding this Lecture • Chapter 38 • Chapter 39 • Chapter 40 • Chapter 46 • Chapter 41

3. Valuable Sources of Data • http://aic.ucdavis.edu/research/datasources.htm • http://www.ers.usda.gov/data-products.aspx • http://www.bls.gov/ • http://www.cdfa.ca.gov/statistics/ • https://www.census.gov/main/www/access.html

4. Importing Data • The data that you work with in Excel can be entered in few basic ways. • You can enter the data by hand one data cell at a time which can be inefficient. • You can copy the data and try to paste it into Excel. • Fortunately, Excel has the capability to import many different types of data. • There are several sources you can import data from: • Access Database • From the Web • From Text • From other Sources

5. Copy and Paste • Sometimes you can input data into Excel by just copying it and then pasting it. • To do this, go to the source document, highlight the data, copy it, and then paste it into Excel. • Sometimes Excel is smart enough to enter the data correctly. • Other times it will bring the data in as a text in a single column. • In class we will work with: • http://www.cdfa.ca.gov/Statistics/PDFs/ResourceDirectory_2013.pdf • http://usda01.library.cornell.edu/usda/nass/SB993/sb957.txt • And Other sites.

6. Importing Data Through the Web • On the Data Ribbon under Get External Data, there is a button that allows you to get data from the Web. • When you press this button, Excel brings up a page that looks like a web page. • From here you can enter the web page that has the data you are interested in importing. • Once you are on the web page, you will see yellow arrows and associated box around what Excel can import. • Click on the yellow arrow to select the data and then click on import. • Try this with the following website: http://www.bls.gov/news.release/cpi.t01.htm.

7. Handling Duplicate Values and Creating Histograms • There are times when your data set has many duplicate values and you are interested in the frequency of those values known as a histogram. • Excel has the ability to remove duplicate values on the Data ribbon. • Once you remove these duplicates, you can use the unique values to develop a histogram.

8. Histogram Activity • In column A, copy the RandBetween() function in cells A1:A5000. • Copy and paste special those values into the C column. • Remove the duplicates from this new column. • Create a histogram out of the original data using the non-duplicate values and the Countif() function. • Press F9 several times to see how the histogram changes.

9. Data Validation • Excel has a tool that can ensure that the data that is being entered is the correct type of data. • This tool is the Data Validation Tool located on the Data ribbon. • The Data Validation tool can check to see if the data entered is a: • Whole number • Decimal • List • Date • Time • Certain text length • Custom (which is based on a formula)

10. Data Validation Cont. • With the Data Validation tool, you can set-up a message that tells the individual what type of data they should enter. • The Data Validation tool allows you to also put an error message in case a correct value is not entered. • You have the option to: • Stop the individual before the wrong data is entered, • Provide warning to the individual about the wrong data, or • Provide information to the individual regarding the wrong data.

11. Different Types of Errors • #DIV/0!—This error alerts you when you have divided by zero. • #N/A—This error alerts you when the information is unavailable. • #NAME?—This error alerts you Excel does not know the name you are using. • #NULL!—This error indicates that the formula you are using does not have an intersection point. • Most of the time this error occurs because you are missing an operator.

12. Different Types of Errors Cont. • #NUM!—This error alerts you to an invalid number, e.g., Sqrt(-100). • #REF!—This error alerts you to an invalid cell reference. • #VALUE!—This error alerts you to incorrect data types, a function’s argument is a range when it should be a single value, etc. • The Iferror() function can handle errors when they come up. • The Iserror() function can check to see if you have an error; it returns true or false.

13. Auditing Toolbar and Formula Auditing • Excel on its Formulas ribbon has a section for formula auditing. • This tool allows you to trace the precedents and dependence of a cell. • It can allow you to toggle between the formulas and the results in a cell. • It can show you how a formula is being evaluated.

14. Circular Reference Errors • When building formulas, you sometimes run into an error known as a circular reference. • This error is alerting you to the fact that the cell you are currently calculating is somehow reliant upon itself causing a circular reference. • Sometimes you can use circular references as a tool for making a calculation, but typically it is an error you need to handle by fixing the circular reference.