![Page 1: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/1.jpg)
Chap 2: Presenting Data in Tables & Charts
Excel Tutorial
![Page 2: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/2.jpg)
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
![Page 3: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/3.jpg)
In the main menu select
Tools/Add-inns
![Page 4: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/4.jpg)
In the Add-inns options Check the first two options
![Page 5: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/5.jpg)
Press ok. This will add the option of Data Analysis in the Tools menu
![Page 6: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/6.jpg)
![Page 7: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/7.jpg)
Frequency Distribution
![Page 8: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/8.jpg)
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.
![Page 9: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/9.jpg)
![Page 10: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/10.jpg)
![Page 11: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/11.jpg)
=f4 –f5
![Page 12: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/12.jpg)
![Page 13: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/13.jpg)
Class boundaries will be prepared manually using the drag option according to the interval of 19
![Page 14: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/14.jpg)
To find the frequencies select
the frequency column.
Do not select the label !!
Then Press the functions button and choose the
option of
Frequency
![Page 15: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/15.jpg)
Select the data array. Costs of electricity bills of 50 houses
(A2:A51)
![Page 16: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/16.jpg)
Add the range of upper class boundaries In the bins array .
(H2:H8)
Press ctrl + Shift + Enter
DO NOT PRESS OK !!
![Page 17: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/17.jpg)
Frequency Distribution of electricity costs of 50
one-bedroom apartments during the month of July
2001.
![Page 18: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/18.jpg)
Calculate the midpoints of the classes by taking
the average of UCB and LCB of each
classM.P of First class= (100.5 + 81.5)/2= 91
![Page 19: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/19.jpg)
![Page 20: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/20.jpg)
Relative Frequency and
Percentage Distribution
![Page 21: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/21.jpg)
![Page 22: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/22.jpg)
To calculate the relative frequency we divide the frequency of the class with the total frequency.
For exampleR.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
![Page 23: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/23.jpg)
Sum of the R.F’s should be equal to 1 or very close to it
![Page 24: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/24.jpg)
To calculate the “relative percentages”
we multiply the R.F’s by 100
![Page 25: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/25.jpg)
Sum of the Relative Percentages should be
equal to 100
![Page 26: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/26.jpg)
Cumulative Percentage Distribution
![Page 27: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/27.jpg)
Add another class at the bottom of the classes
![Page 28: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/28.jpg)
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
![Page 29: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/29.jpg)
![Page 30: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/30.jpg)
Graphical Presentation of Data
![Page 31: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/31.jpg)
• 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
![Page 32: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/32.jpg)
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
![Page 33: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/33.jpg)
![Page 34: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/34.jpg)
Select Tools/data analysis from the main menu
![Page 35: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/35.jpg)
Select Histogram from the Data analysis menu
![Page 36: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/36.jpg)
Check the chart output check box
PRESS OK
Select the Upper class boundaries as the bin
range
Select the input range as the 50 sample values
![Page 37: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/37.jpg)
This is the Histogram for the data but it
requires some adjustments as there
should be no gaps between the bars of the
Histogram
![Page 38: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/38.jpg)
Right click on the bars of the Histogram and select the option of Format Data series
![Page 39: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/39.jpg)
Select Options and reduce the gap
width to 0
![Page 40: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/40.jpg)
![Page 41: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/41.jpg)
There is no gap between the bars
now
![Page 42: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/42.jpg)
Make other necessary adjustments for better
presentation
![Page 43: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/43.jpg)
![Page 44: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/44.jpg)
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.
![Page 45: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/45.jpg)
Choose the chart wizard from the main menu. Select line graphs and then select the
first option In the second rowfor making the line graphs
and then click next.
![Page 46: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/46.jpg)
Select the relative percentages in the
data range.
![Page 47: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/47.jpg)
Select Series option.In Category (X) axis labels enter the mid
points range. Then Click Next
![Page 48: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/48.jpg)
Add titles for the X and Y axis series
and the chart titles. Then click finish
![Page 49: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/49.jpg)
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
![Page 50: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/50.jpg)
![Page 51: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/51.jpg)
To obtain the Cumulative Percentage Polygon. We repeat the same process
by selecting C.P’s in place of R.P’s
![Page 52: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/52.jpg)
![Page 53: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/53.jpg)
![Page 54: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/54.jpg)
Mid point of the additional class has
also been calculated
![Page 55: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/55.jpg)
![Page 56: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/56.jpg)
• For the purpose of making• Bar chart • Pie chart
(Used for presenting “Qualitative Data”)• We use the following table (Table 2.7 on page
61 of the book)
Fund Objective No of Funds % of Funds
Growth & Income 26 18.98
International 42 30.66
Mid cap 20 14.6
Small Cap 37 27.01
Technology 12 8.76
Total 137 100
![Page 57: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/57.jpg)
From the chart wizard in the menu select the
option of bar chart then click Next
![Page 58: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/58.jpg)
In the Data Range select the series (in this case the number of funds in each
category)Then select the Series
option on the top of the window
![Page 59: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/59.jpg)
In the category (X) axis labels input the labels range (in this
case the fund objective column).
Then click Next
![Page 60: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/60.jpg)
Add the suitable titles and click Next /Finish
![Page 61: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/61.jpg)
![Page 62: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/62.jpg)
Make necessary changes to make the
chart more presentable.
![Page 63: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/63.jpg)
Bar chart can also be made for two or more series. By using other
options in the bar chart menu.
![Page 64: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/64.jpg)
In order to make the pie chart select the
option of pie from the chart wizard
![Page 65: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/65.jpg)
In the Data Range select the series (in this case percentage of funds in
each category).Then select the Series
option.
![Page 66: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/66.jpg)
In Category labels select the label series (in this case the fund
objective). Then click Next
![Page 67: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/67.jpg)
Add a suitable title for the chart
![Page 68: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/68.jpg)
In the data labels check the options of category name and
percentage or whatever is suitable.
Then click Next
![Page 69: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/69.jpg)
Color scheme and other aspects of the
chart can be changed by right clicking on
the chart.
![Page 70: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/70.jpg)
• In order to prepare a “Pareto Chart” we use problem 2.29 in the book on page 66.
• Data table is as follows
Reasons for Failure Frequency
Physical Connection 1
Power Failure 3
Sever Software 29
Server Hardware 2
Server out of Memory 32
In adequate Bandwidth 1
![Page 71: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/71.jpg)
![Page 72: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/72.jpg)
Select PHStat /Descriptive Statistics /One-way
Tables & ChartsFrom the main menu of
PHStat
![Page 73: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/73.jpg)
In the types of Data select the table of
frequencies
In the table of Cell Range select the
complete table and check the option of “first row contains
labels”
Check the option of Pareto Diagram and press ok
![Page 74: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/74.jpg)
This adds three sheets on the menu of excel work sheet. The sheet named Pareto contains
this Pareto diagram.
![Page 75: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/75.jpg)
The one way table shows the necessary calculations. Enhance the table & diagram for
better presentation.Delete the sheet
named “DataCopy”
Pareto diagram and this table shows that 90% of the time failures occur due to
only two reason Server out of Memory
Server Software
![Page 76: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/76.jpg)
![Page 77: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/77.jpg)
![Page 78: Chap 2: Presenting Data in Tables & Charts Excel Tutorial](https://reader036.vdocuments.us/reader036/viewer/2022062309/5697c0211a28abf838cd2c75/html5/thumbnails/78.jpg)
Using the same procedure bar chart and
pie chart can also be prepared for the
“Qualitative Data”Using the PHStat menu