Day 12 presentation

CSE 101 ? Day 12 Data Acquisition; Chart Types CSE 101 - Common Material - Day 12 Overview of Day 12 Data acquisition Web tables Charting Selecting chart types One or two y-axes? Conditional results Use of the IF function Homework and BT preparation CSE 101 - Common Material - Day 12 Review Question In a payroll spreadsheet, you want to see what the gross pay amounts (in column J) are after a raise of 4.25%. (You will put these raised amounts in a new column.) How should you best do this? Why? A formula like =J5*1.0425 A formula like =J5 + (J5*4.25%) Insert a row at the top, set B1 to 4.25%, create formulas like =J5 + (J5*B1) Insert a row at the top, set B1 to 4.25%, create formulas like =J5 + (J5*$B$1) CSE 101 - Common Material - Day 12 Acquiring Data Spreadsheets are useful in organizing and analyzing data; but where does the data come from? Some "new data" are obtained via experiments, observations, etc. Most data are actually obtained elsewhere, but are made available on-line for others to use Often such data are simply included within the text of a Web page; these must be gathered a piece at a time and manually typed into a spreadsheet Sometimes, however, the data are made available via tables on a Web page; these can be gathered as a WHOLE using Excel We will look at how to acquire table data from Web pages next Open a new, empty Excel workbook CSE 101 - Common Material - Day 12 Pulling in Web Page Table Data Browse (in a new Firefox tab) to: http://www.worldbank.org/depweb/english/modules/economic/gnp/data.html Or use the World Bank link on today's Classwork page Click on some of the links and notice the useful data presented in tables Use a Google search (or Excel HELP) to learn how to "pull data from a Web page into Excel" (starting from Excel) You will probably find the word "query" in the results Read the directions and discuss them with your partner(s) CSE 101 - Common Material - Day 12 First Data Acquisition Exercise On the World Bank web page, click on the South America link under Economic Data Tables Put your cursor in cell A2 of an empty Excel worksheet Follow the directions on using a Web query to pull in the data table Use the URL showing in your browser as your data source Under Options? in the Query window, select Full HTML formatting to preserve colors, sizes, fonts, etc. Click the arrow next to each table to be imported; a green check mark will be displayed In cell A1, enter the data source URL you used Rename the worksheet Econ Data ? S Am Right-click on the tab and select Rename Save your spreadsheet into your personal cse101 AFS folder as data_charts.xls CSE 101 - Common Material - Day 12 Second Data Acquisition Exercise Save your spreadsheet; then put your cursor in cell A2 of an empty worksheet On the World Bank web page, click on the South America link under Basic Data Tables Follow the directions used earlier to pull in the main data table from this web page (including colors, etc.) into your worksheet Sometimes other information comes along with the table that you want; in that case, delete those rows/columns In A1, enter the data source URL you used Rename this worksheet Basic Data ? S Am Save (and back up) your spreadsheet again CSE 101 - Common Material - Day 12 Choosing a Chart Type Read the pages linked to today's Classwork page that are concerned with chart types and when each type should be used Work with your partner to answer: When is a pie chart a good/bad choice? What are the differences between bar and column charts? When is a line chart often used? CSE 101 - Common Material - Day 12 Chart of GNP Totals Create a pie chart showing GNP Totals for each South American country Be sure the legend is based on actual data Give your chart a meaningful title Explore some of the chart options in order to display the percentage represented by each "slice" of the pie Put the chart on its own chart sheet and name the sheet GNP Totals Pie Chart Use View -> Sized with window to maximize it How informative is your chart? Can you tell who is the economic powerhouse? What other questions can you answer from it? CSE 101 - Common Material - Day 12 Charting GNP vs Growth We wish to answer the question: Do countries with big economies also have fast- growing economies? To do so, chart two series - GNP Total and GNP Growth What is an appropriate type for this chart? Why? Title the chart and all axes appropriately Is a legend necessary/helpful? Why? Put the chart on its own chart sheet named GNP vs Growth Chart and maximize the chart What other questions can you answer? CSE 101 - Common Material - Day 12 GNP Growth vs Pop Growth To try to answer the question: Are economic and population growth related? Chart two series - GNP Growth and Population Growth What is an appropriate type for this chart? Are two y-axes needed? Why? How do you select series from TWO worksheets? Title the chart and all axes appropriately Is a legend necessary/helpful? Why? Put the chart on its own chart sheet named GNP Growth vs Pop Growth Chart;maximize the chart Can you answer the question above? Save, close and back up your spreadsheet CSE 101 - Common Material - Day 12 Conditional Results In some cases, you may want to compute a result based on a condition Pass/Fail class ? if the student scored 70% or better, then display ?pass?, else display ?fail? Default value ? if the response was blank, then display ?No answer?, else display the response Graduated tax ? if the gross pay is above $40,000, then the tax rate is 20%, else it is 16% All of these are of the form if condition is true then do one action else do another action CSE 101 - Common Material - Day 12 IF Function IF(logical_test, true_value, false_value) logical_test: Must be either True or False for any set of data Example: B2 >= B3 + B4 true_value: value used when the test is True false_value: value used when the test is False Both true_value and false_value can be anything that a cell can hold: text, number, or formula Text values must be enclosed in "double quotes" Exactly ONE of true_value or false_value will be displayed/used CSE 101 - Common Material - Day 12 IF Function Example If a value is less than 0.5, show ?half empty?, else show ?half full? =IF(cell < 0.5, "half empty", "half full") =IF(A3 < 0.5, ?) half full 0.8 3 =IF(A2 < 0.5, ?) half empty 0.2 2 =IF(A1 < 0.5, ?) half full 0.5 1 (formula in B) B A CSE 101 - Common Material - Day 12 IF Function Exercise Open your grades workbook We wish to give a 10-point bonus for students having ?excellent? attendance Add an Attendance Bonus column ? where? Read HELP on operators to learn how to express comparisons (?less than? etc.) Create a formula that returns 10 if the student attended 25 or more days and 0 otherwise Update formulas in the Total Points column to include the Attendance Bonus points Save and backup your file CSE 101 - Common Material - Day 12 Homework and SIRS Be sure to check the Homework page linked to today's Classwork page Please take time at the end of class to fill out the SIRS linked to the Classwork page too, if you have not yet done so Please submit SIRS by the end of Day 12 CSE 101 - Common Material - Day 12 Day 13 Bridge Task INDIVIDUAL - not group work All students should come for a BT You will get the next BT that you have not yet passed You should have available backup copies of all files you have worked on that are appropriate for the BT you will be taking CSE 101 - Common Material - Day 12 Bridge Task 2.0 Covers all spreadsheet material Including material on chart types, data acquisition, and the IF function Be sure to have all classwork and homework available for the BT Finish all exercises you did not complete during class and all homework Have ALL files backed up! Review the Sample 2.0 BT and the Guidelines CSE 101 - Common Material - Day 12 To Take a Bridge Task? You MUST have a photo ID (on-screen or other) in order to take ANY BT You MUST go to your assigned section to take in-class BTs CSE 101 - Common Material - Day 12