Communicating Quantitative Information

1. Communicating Quantitative Information Spreadsheet lesson: unit pricing Graphs (Exercise: coin flips) Homework: Spreadsheet & graph assignment. Continue postings.

2. News? • Primary • Do vote. New voting machines. You can make posting on old versus new. • Predictions on results? • Results? • ?

3. Unit pricing • Prices for products are $/quantity • cost/unit • Generally, more costs more but….often not that much more • $1/bagel unit cost is $1/bagel • $5/dozen bagels unit cost is ….

4. answer is • .41666666 • round off to .42, 42 cents • Okay to round off, but you need to know you are doing it. • Reasonability check: if it were $6/dozen, the answer would be….. • 6 dollars for 12 • 50 cents for 1

5. Excel basics • Excel grid: • columns use letters: A…Z, AA, AB… • rows are numbered • Cell can hold • Text • Number • Formula: can refer to other cells • Cell can be formatted • Number • Centering, justification • Color • Font, etc.

6. Cookie lab • Create columns for each type of cookie • Label (text) rows • Record number of cookies eaten • Use information on box to enter • cost/box, cookies/box, calories/serving, etc. • Use formulas to compute cost/cookie, calorie/cookie • Use formulas to compute YOUR cost and calories • Use formulas (sums) to compute total cost and calories

7. Copy-and-paste • Can copy any cell • Copy-and-paste formula with cell references modifies new references • Relative addressing

8. Cookie lab • For second, and subsequent cookies • Use copy-and-paste for formulas • NOT for data • What values are formulas and what data specific to each cookie?

9. Excel basics • Built-in functions • Sum • Average • others • Watch out for default/automatic action. For example, make sure range is what you want. =SUM(B2:D2)

10. Excel basics • Changing cell(s) forces immediate re-calculation of any formula using that cell. • Inserting a new cell forces immediate re-calculation of any formula using a range including the new cell. • Cookie lab: • Try it: change number of cookies eaten. Add a column for a new cookie

11. Cookie lab • Add formatting • Special conventions for money • Note: data remains the same regardless of what you see, so you can change • Add taxrate • Use either absolute addressing ($A20) • Use named cell • Add ????

12. Excel formatting • Fonts • Text size • Column & Row size • Hide columns and rows • Decoration • View/Toolbars/Drawing • Color

13. Excel basics, cont. • Copy and paste of cell formulas will adjust any [regular] cell reference =B10/B11 in column B will by changed to =C10/C11 when copied and pasted to column C • This is generally what you want. • If not: use absolute references =$B$10/B11 in column B will be changed to =$B$10/C11 when copied and pasted to column C

14. Excel graphs • Bar (comparable values) • Line (time series, other dependencies) • Pie (parts of a whole) • Others What graph makes sense? When is a graph better than words or a table? …. More data

15. Create Pie chart: parts of a whole • Select data (cells with calories, NOT total) • Click on graph icon • Select pie, accept first pie choice & click on Next • Click on Series, click in Category Labels box. Select names of Cookies in main spreadsheet. Enter. Next. • Check off Category name, percentage boxes. • Finish. • May need to drag graph to position on spreadsheet.

16. Create Line graph • Select Data and click on graph icon. • Choose Line, pick subcategory. Next. • Click on series. For Category name: select column labels (e.g., months). • For each series (now called series 1, series 2, etc.): click in Name box, Select Row label. Enter. • Next. Finish.

17. Spreadsheet • “killer ap” for personal computers • People could examine data • Do ‘what if’ experiments • Produce professional tables and graphs.

18. Distribution • … record counts of each value reports • individually or in ranges • Excel: can generate distributions using the frequency function. This requires special keystrokes since it is an array function. • =frequency(data,bins)

19. Scores from class • Assume final scores are in a2:a25 • High end of ranges (bins) are in c2:c10. Will reference c11 because one more bin is inferred. • Type into f2 =frequency(a2:a25,c2:c11) Select f2 to f12 Press function key f2. Press CTL-SHIFT-ENTER This produces the frequency counts. Sorry about coincidence!

20. Graph • Write into G2 through G12 the grades corresponding to these totals: F, D-, D, … up through A. • Select F2:F12 and click on Chart/Graph icon. Choose bar graph (columns). Next. Enter in a title. Click next and finish. May need to re-position chart. Right-click, choose source data. Click on Category (x) axis labels. Select G2:G12.

21. Frequency: array formula • Scores  bins  histogram • =frequency(data, range_upper_bounds) • range_upper_bounds contains 1 more slot: those scores > last number • Need to use special keystrokes to enter as array formula • f2, shift-control-enter

23. Results from Fall class

24. Excel files http://faculty.purchase.edu/jeanine.meyer /quant/exampledistribution.xls http://faculty.purchase.edu/jeanine.meyerquant/simpledistribution.xls

25. Binomial Distribution • Note: when flipping coins, any single sequence of heads and tails is equally likely! • H H H H H H H H H H • H T H T H T H T H T • There are more sequences with 5 heads and 5 tails than any other combination • will return to this topic later. Extra credit to make a posting explaining the formula. • When flipping coins in a sequence (assuming a fair coin), the odds of heads on the next flip is ½ no matter what the sequence has been.

26. Class Exercise • Flip a coin 10 times. Record number of heads. • What are possible values? • I will record data points and we will see the distribution evolving…

27. Bar graph • Select data. Click on graph icon. Choose bar graph… • Right click. Enter column/row with suitable labels for Category (x) axis.

28. Homework • Choose one of the following: • Personal or Institution or Government budget • Columns are months & Rows are expenses and income • Include sums, net, averages • Graphs: pie charts on expenses, stacked bar or line graph on expenses and income. • Sports record(s) • Columns are years & Rows are name of record. Group so unit of cell is common: times, distance, weights, etc. • ? (Confirm with me)

29. Homework, continued. • May work in pairs • More expected from pairs than individuals. • Prepare spreadsheet and graphs • Decide on positive and negative aspects of graphs for communicating your situation. • Keep up on postings.