Excel user guide

Fourth step - The fourth step will allow you to locate your chart either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a Component bar chart like the one shown below.

Percentage Bar Chart

In order to construct a Percentage bar chart using Excel you will have to follow the following steps.

A Percentage bar chart is very similar to a component bar chart. The procedures are the same but instead the stacks represent 100% and the components change proportionately within the bar.

Below I have a set of data showing the value of output per worker in three different countries.

992

996

2000

France

28

20

18

Germany

08

09

09

Japan

03

06

97

Once you have inputted data in the spreadsheets highlight the data and click on the bar chart icon as you did for the simple bar chart.

Once you have selected the bar chart icon a dialog box will appear with the title chart wizard.

Steps to construct a Percentage bar chart

First step - Select column for the chosen bar type. You will be presented with a chart sub type on the right hand side of the dialog box, select the 100% stack format. Then select Next.

Second step - The second step will illustrate the chart source data within the bar chart. This will allow you to arrange or edit your data. This will also allow you to change the format in which you would like your data to be presented. Select either row or column. Once you had double-checked your data select Next.

Third step (carryout the same procedure as shown in previous third steps) - The chart option will now appear in the third step. Here you will be able to name your Y and X-axis. If you prefer the names of countries below the bar chart select legend then select placement bottom. Once you have named your axis select Next.

Fourth step - The fourth step will allow you to locate your chart either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a Percentage bar chart like the one shown below.

Pie charts

Pie Bar Chart

In order to construct a Pie bar chart using Excel you will have to follow the following steps.

Once again you will need some sort of data in order to construct a pie chart. Below is a table with data on Privatisation Proceeds by sector (1990 - 2000).

Telecoms

36

Power

6

Oil & Gas

0

Transport

6

Financials

5

Other

7

Source: Privatisation International, October 2000 Issue No. 145, p.7.

Once you have inputted data in the spreadsheets highlight the data and click on the bar chart icon as you did for the simple bar chart.

Steps to construct a Percentage bar chart

First step - Select Pie for the chosen bar type. You will be presented with a chart sub type on the right hand side of the dialog box, select the standard format Pie. Then select Next.

Second step - The second step will illustrate the chart source data within the bar chart. This will allow you to arrange or edit your data. This will also allow you to change the format in which you would like your data to be presented. Select either row or column. Once you had double-checked your data select Next.

Third step - The chart option will now appear in the third step. Here you will be able to name the title of the pie chart.

Now select Data labels > percentage > category name.

Then select legend and deselect show legend

Once you have done this select Next.

Fourth step - The fourth step will allow you to locate your chart either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a Percentage bar chart like the one shown below.

Histogram

Histogram - Equal class widths

In order to construct a Histogram with equal class width using Excel you will have to follow the following steps.

A Histogram consists of grouped continuous data like shown in the box below.

43 151 146 148 149 150 145 152 153 149

48 149 150 150 152 150 151 144 147 150

47 149 150 148 150 151 150 146 148 152

49 150 149 147 151 148 149 151 151 150

Data from Dr. Kaoru Ishikawa's Guide to Quality Control. UNIPUB/Quality Resources 1982

Source: http://www.carillontech.com/QET_101/Histogram_Data.html

Type in the grouped data above into the excel spreadsheet.

Once you have typed in the grouped data above the grouped continuous data has to be divided into upper and lower class boundaries like shown in the table below.

Lower class Boundary

Upper class Boundary

43

44

44

45

45

46

46

47

47

48

48

49

49

50

50

51

51

52

52

53

Create a table in excel with the heading Lower class boundary and Upper class boundary. Once you have done this enter the following data above.

A simple way of saving time from typing up the Lower and upper class boundary data is to create a simple formula.

a) select column A9

b) Type (=A9+1)

c) Then select column A9 and select the bottom right corner of that cell and drag it down 9 columns

This will automatically number the empty cells with the lower class boundary. Now do the same with the upper class boundary.

Finding the frequency

Create a new column next to the upper class boundary column called frequency. Highlight eleven columns down. Then select the function icon.

Once you have selected the function icon and dialog box will appear with the title paste function. The paste function dialog box will consist of function category and function name.

The function category will be All

The function name will be frequency

Then select ok.

A function Argument dialog box should now appear. This will allow you to highlight the continuous grouped data and find out the frequency. You will also have to highlight the upper class boundaries. In order to do this you will have to do the following:

a) Click the box on the right of the data_array text box. This will bring you back to the spreadsheet allowing you to select the continuous grouped data.

b) Click the box on the right of the Bins_array text box. This will bring you back to the spreadsheet allowing you to select the upper class data column.

Important: once you have done the above hold down Alt + Shift then click ok

Frequency

2

2

3

5

7

0

6

3

0

You now will be presented with the frequency in the frequency column of your table. Do not delete the bottom cell, which should contain the number 0. This is there for calculation purposes.

Steps to construct a Histogram

Now that you have found the frequency of the lower and upper class boundaries you will need to create a histogram to go with it. We will be creating a histogram within the format of bars.

Before you create your histogram you will need to enter the lower and upper class boundaries in the spreadsheet like shown below.

Upper and Lower class boundaries

43 - 144

44 - 145

45 - 146

46 - 147

47 - 148

48 - 149

49 - 150

50 - 151

51 - 152

52 - 153

Highlight the frequency data and once again click on the bar chart icon

Steps 1 - Follow the same step as the previous simple bar chart for step 1

Step 2 - click on series, this section will allow you to label the X axis of the histogram bars. In order to do this click here

Once you have clicked here you will then have to highlight the lower and upper class boundaries that you just entered in the spreadsheet.

Step 3 - The chart option will now appear in the third step. Here you will be able to name your Y and X-axis. Once you have named your axis select Next.

Step 4 - The fourth step will allow you to locate your histogram either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a histogram like the one shown below.

Editing the histogram

To emphasise the continuity of the variable axis, the gaps must not be left between the bars in a histogram.

) As you can see there are gaps in between the bars of the histogram in order to get rid of these gaps you will have to double click the bar. This will take you to a format data series dialog box. Once this box appears select options.

2) Now double click the upper and lower class boundary data below the bar chart. This will take you to a format Axis menu. Select font and change the font size to 8.5. Changing the size of the font will allow the boundary data to fit into one bar like shown below.

Histogram - Unequal class widths

In order to construct a Histogram with unequal class width using Excel you will have to follow the following steps.

Using the same continuous data from the equal class width histogram,

43 151 146 148 149 150 145 152 153 149

48 149 150 150 152 150 151 144 147 150

47 149 150 148 150 151 150 146 148 152

49 150 149 147 151 148 149 151 151 150

Data from Dr. Kaoru Ishikawa's Guide to Quality Control. UNIPUB/Quality Resources 1982

Source: http://www.carillontech.com/QET_101/Histogram_Data.html

Create an upper and lower class boundary table with the following data below into the spreadsheet.

Lower class Boundary

Upper class Boundary

frequency

Class width

43

45

3

20

45

46

2

0

46

47

3

0

47

48

5

0

48

49

7

0

49

50

0

0

50

51

6

0

51

53

4

20

= boundaries that have been combined

Frequency Density

Now we have to find out the frequency density. Create a new column called frequency density next to class width.

In order to find out the frequency density you will have to enter the following formula in the first cell of that column.

LCB

UCB

Frequency

class width

frequency density

43

45

3

20

0.15

45

46

2

0

0.2

46

47

3

0

0.3

47

48

5

0

0.5

48

49

7

0

0.7

49

50

0

0

50

51

6

0

0.6

51

53

4

20

0.2

Once you have entered the formula drag the bottom right corner of the cell down to calculate the rest of the data automatically and you should obtain the following data in the below table.

Now create a new column called how many?

This column will consist of a formula such as:

How many? = Frequency / class width of 10

In order to do the formula above in excel spread sheet you will have to enter the formula as follows.

This formula should give you the data in the table below.

LCB

UCB

Frequency

class width

frequency density

How many?

0.3

43

45

3

20

0.15

0.3

0.3

45

46

2

0

0.2

0.2

0.2

46

47

3

0

0.3

0.3

0.3

47

48

5

0

0.5

0.5

0.5

48

49

7

0

0.7

0.7

0.7

49

50

0

0

50

51

6

0

0.6

0.6

0.6

51

53

4

20

0.2

0.4

0.4

0.4

Steps to construct a Histogram - unequal class width

Now that you have found all the data that is need. We will be creating a histogram within the format of bars.

Before you create your histogram you will need to enter the lower and upper class boundaries in the spreadsheet like shown below.

Upper and Lower class boundaries

43 -

45

44 - 146

45 - 147

46 - 148

47 - 149

48 - 150

49 - 151

51 -

53

Highlight the last column that you have created with data and once again click on the bar chart icon

Steps 1 - Follow the same step as the previous simple bar chart for step 1

Step 2 - Follow the same step as the previous Histogram chart for step 2

Step 3 - the same step as the previous Histogram chart for step 3

Step 4 - The fourth step will allow you to locate your histogram either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a histogram like the one shown below

Editing the histogram

To emphasise the continuity of the variable axis, the gaps must not be left between the bars in a histogram.

) Follow the same procedures in the previous histogram for discarding the gaps in between the bars.

2) Follow the same procedures in the previous histogram for changing font size of text in the x axis.

3) Now we have to combine the first two bars and last two bars. In order to do this you have to extract the lines from these bars.

a) Click on to the first bar so that the bar is highlighted; once you have done this double click that bar. This will take you to an option called format data point as shown in the picture below. Then select None in the boarders section,

Once you have carried out these instructions do the same with the other three bars and you should be left with a histogram like the one below.

Cumulative Frequency polygon

On a graph, it can be represented by a cumulative frequency polygon, where straight lines join up the points, or a cumulative frequency curve.

These data are used to draw a cumulative frequency polygon by plotting the cumulative frequencies against the upper class boundaries.

LCB

UCB

Frequency

Cumulative Frequency

43

44

2

2

44

45

(2+1) 3

45

46

2

(2+3) 5

46

47

3

8

47

48

5

3

48

49

7

20

49

50

0

30

50

51

6

36

51

52

3

39

52

53

(39+1) 40

Creating Cumulative Frequency polygon

In order to create a cumulative Frequency polygon you will have to follow the following steps.

. Input the data of in the table above. Once you have done this select the bar chart icon

2. Once you're in the chart wizard select the Bar type: XY (scatter) and scatter graph with data points connected. Then click next.

3. Now you will highlight the relevant data for the cumulative frequency polygon which is the upper class boundaries and cumulative frequency. Once you have done this click next.

4. Now you will be able to name the chart title, x axis and y axis. Once you have done this click on next.

5. Locate your graph either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a cumulative frequency polygon like the one shown below

Scatter Diagram - with line of best fit

A Scatter Diagram is used to interpret data by graphically displaying the relationship between two variables.

Age, x

22

30

25

35

65

50

27

53

42

Days missed,y

0

4

2

4

7

3

8

6

Source: http://www.pccc.cc.nj.us/math/tech4.htm

In order to create a scatter graph you will need to do the following:

Using the data that in the table above

* Choose the Chart Wizard icon from the tool bar (Figure 3). If the Chart Wizard is not visible, you can also choose Insert > Chart...

The first dialogue of the wizard comes up

* Choose XY (Scatter) and the unconnected point's icon for the Chart sub-type.

* Click Next >

The Data Range box should reflect the data you highlighted in the spreadsheet. The Series option should be set to Columns, which is how your data is organised.

* Click Next >

The next dialogue in the wizard is where you label your chart

* Enter Missed days for the Chart Title

* Enter Age for the Value X Axis

* Enter Days missed for the Value Y Axis

* Click Next >

Locate your graph either within the spreadsheet you entered your data in or in a new spreadsheet. Then select finish.

Once you have selected finish you should have a scatter diagram polygon like the one shown below

Line of best fit

When the chart window is highlighted, besides having the chart floating palette appear, a Chart menu also appears. From the Chart menu, you can add a regression line to the chart.

* Choose Chart > Add trendline...

A dialogue box appears.

* Select the Linear Trend/Regression type

* Choose the Options tab and select Display equation on chart

* Click OK to close the dialogue

The chart now displays the regression line

In order to calculate the line of best fit from the data that you have inputted use the formula below.

n?xy - ?x?y

r=

V n?x2 - (?x) 2)(n?y2 - (?y) 2)

Where n is number of pairs data

x is independent variable

y is dependent variable

? means take the sum of

Age, x

Days missed,y

xy

x2

y2

22

0

0

484

0

30

4

20

900

6

25

25

625

35

2

70

225

4

65

4

910

4225

96

50

7

350

2500

49

27

3

81

729

9

53

8

424

2809

64

42

6

252

764

36

Total

349

45

2232

5261

375

(?X)2

764

36

n

6615

(10*Y2-(eX)2

0*3714

SQRT

0

50846

3714

560242044

23669.43

y=bx+a

n*23669.43

r =

0.279474379

6615/150846

b =

6615

=

0.043852671

50846

a =

2.969541784

y =

3.013394455

Raw data

Mode

In order to find the mode from the frequency distribution you need to use the formula below

Mode = L + f1 - f0 c

2f1 - f0 - f2

Where: L = lower boundary of the modal class

C = interval of the modal class

F0= frequency of the class before the modal class

F1= frequency of the model class

F2 =frequency of the class after the model class

LCB

UCB

frequency

43

44

2

44

45

45

46

2

46

47

3

47

48

5

48

49

7

49

50

0

50

51

6

51

52

3

52

53

0

* Using the data raw data from the histogram in the first section of the manual find the following data in the table below

* Once have found the relevant data for each letter use the formula on the previous page to calculate the mode and you should get a answer like the one in the table below

L

49

C

f0

49

f1

0

f2

51

mode

49.49642857

Standard Deviation

Standard deviation is a measure of the range of values in a set of numbers. Standard deviation is a statistic used as a measure of the dispersion or variation in a distribution, equal to the square root of the arithmetic mean of the squares of the deviations from the arithmetic mean.

The formula for standard deviation is shown below

Standard deviation = Efx2/Ef - (Efx /Ef)2

The table below will provide you with data which will enable you to calculate the standard deviation.

LCB

UCB

frequency

Mid point (x)

fx

fx2

43

44

2

43.5

287

41184.5

44

45

44.5

44.5

20880.25

45

46

2

45.5

291

42340.5

46

47

3

46.5

439.5

64386.75

47

48

5

47.5

737.5

08781.25

48

49

7

48.5

039.5

54365.75

49

50

0

49.5

495

223502.5

50

51

6

50.5

903

35901.5

51

52

3

51.5

454.5

68856.75

52

53

52.5

52.5

23256.25

0

5944

?F

40

5944/40 =

48.6

x*fx=fx2

standard deviation = Efx2/Ef - (Efx /Ef)2 =

882981.2

Simple and compound interest

Simple

In order to find a simple 5% interest cost of £1000 within the time of 8 years you will have to do the following:

The simple formula is A= P (1+ni)

In the spreadsheet type in the following data

Interest

£ (p)

Percentage (i)

Years (n)

000

0.05

8

Once you have entered the data into the spread sheet select a cell and enter the simple formula which should be like the one below.

A = 1000 (1+8*0.05)

Now you should have the results for the simple interest

Simple

A =

400

Compound

Using the same data you will be able to calculate the compound interest of 5% of £1000.

The compound formula is A= P (1+i)n

Enter the following data into the spreadsheet.

Begin

End

£1000

050

Once you have entered this data you will have to enter the compound formula in the cell under End 1050.

A = 1000(1+0.05)8

Bibliography

Title

Author

ISBN

Publisher

Guide to Quality Control

Dr. Kaoru Ishikawa's

UNIPUB/Quality Resources 1982

Economics Today

Privatisation International

SDF Quantitative methods Assignment

Armando Leonardo