1 / 78

Chap 2: Presenting Data in Tables & Charts

Chap 2: Presenting Data in Tables & Charts. Excel Tutorial. Analysis Tool Pack. In order to obtain Data analysis option (which contains various statistical functions) in your Excel menu you need to install “Analysis Tool Pack” In order to Install ITP follow the following commands.

majed
Download Presentation

Chap 2: Presenting Data in Tables & Charts

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. Chap 2: Presenting Data in Tables & Charts Excel Tutorial

  2. Analysis Tool Pack • In order to obtain Data analysis option (which contains various statistical functions) in your Excel menu you need to install “Analysis Tool Pack” • In order to Install ITP follow the following commands

  3. In the main menu select Tools/Add-inns

  4. In the Add-inns options Check the first two options

  5. Press ok. This will add the option of Data Analysis in the Tools menu

  6. Frequency Distribution

  7. Example 2.12 (pg # 57) • Using Excel to make • Frequency Distribution • Percentage Distribution • Cumulative Percentage Distribution • Solution: • We take the class interval as 7. Which means that there will be 7 classes in the frequency distribution of electricity costs during the month of July for the sample of 50 one-bedroom apartments.

  8. =f4 –f5

  9. Class boundaries will be prepared manually using the drag option according to the interval of 19

  10. To find the frequencies select the frequency column. Do not select the label !! Then Press the functions button and choose the option of Frequency

  11. Select the data array. Costs of electricity bills of 50 houses (A2:A51)

  12. Add the range of upper class boundaries In the bins array . (H2:H8) Press ctrl + Shift + Enter DO NOT PRESS OK !!

  13. Frequency Distribution of electricity costs of 50 one-bedroom apartments during the month of July 2001.

  14. Calculate the midpoints of the classes by taking the average of UCB and LCB of each class M.P of First class = (100.5 + 81.5)/2 = 91

  15. Relative Frequency and Percentage Distribution

  16. To calculate the relative frequency we divide the frequency of the class with the total frequency. For example R.F of first class = 4/50 (G3/G$10) We add the dollar sign so that the denominator stays the same when we drag the cursor down

  17. Sum of the R.F’s should be equal to 1 or very close to it

  18. To calculate the “relative percentages” we multiply the R.F’s by 100

  19. Sum of the Relative Percentages should be equal to 100

  20. Cumulative Percentage Distribution

  21. Add another class at the bottom of the classes

  22. C.P shows the % of values falling below a particular class. C.P for the first class is 0. For remaining classes it is obtained by adding R.P and C.P of the previous class

  23. Graphical Presentation of Data

  24. Different graphs and charts are used for the graphical presentation of data. In this demonstration we shall cover • Histogram • Percentage Polygons • Cumulative Percentage Polygon • Bar Chart • Pie Chart • Pareto Diagram

  25. For making • Histogram • Percentage Polygon • Cumulative Percentage Polygon Which are the graphical tools for presenting Quantitative Data We use example 2.12 on page 57

  26. Select Tools/data analysis from the main menu

  27. Select Histogram from the Data analysis menu

  28. Select the input range as the 50 sample values Select the Upper class boundaries as the bin range Check the chart output check box PRESS OK

  29. This is the Histogram for the data but it requires some adjustments as there should be no gaps between the bars of the Histogram

  30. Right click on the bars of the Histogram and select the option of Format Data series

  31. Select Options and reduce the gap width to 0

  32. There is no gap between the bars now

  33. Make other necessary adjustments for better presentation

  34. Percentage Polygon is formed by having the midpoint of each class represent the data in that class and then connecting the sequence of midpoints at their respective class percentages.

  35. Choose the chart wizard from the main menu. Select line graphs and then select the first option In the second row for making the line graphs and then click next.

  36. Select the relative percentages in the data range.

  37. Select Series option. In Category (X) axis labels enter the mid points range. Then Click Next

  38. Add titles for the X and Y axis series and the chart titles. Then click finish

  39. Enhance the diagram by removing unwanted labels and using better coloring. Optionally, to remove/change background color, select the graph – right click – select Format Plot Area

More Related