1 / 29

Communicating Quantitative Information

Communicating Quantitative Information. Spreadsheet lesson: unit pricing Graphs (Exercise: coin flips) Homework: Spreadsheet & graph assignment. Continue postings. News?. Primary Do vote. New voting machines. You can make posting on old versus new. Predictions on results? Results? ?.

nikkos
Télécharger la présentation

Communicating Quantitative Information

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

  22. Results from Fall class

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

  24. 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.

  25. 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…

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

  27. 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)

  28. 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.

More Related