Chapter III. Microsoft Excel Basics, 2019.ppt

Introduction to Excel

• Excel is a computer program used to create electronic spreadsheets.

• Within excel user can organize data ,create chart and perform calculations.

• Excel is a convenient program because it allow user to create large spreadsheets, reference information, and it allows for better storage of information.

• Excels operates like other Microsoft (MS) office programs and has many of the same functions and shortcuts of other MS programs. • Microsoft excel consists of workbooks. Within each workbook, there

is an infinite number of worksheets.

• Each worksheet contains Columns and Rows.

• Where a column and a row intersect is called a cell. For e.g. cell B4 is located where column B and row 4 meet.

OFFICE BUTTON CONTAINS

• NEW-TO OPEN NEW WORKBOOK. (CTRL+N)

• OPEN-TO OPEN EXISTING DOCUMENT (CTRL+O)

• SAVE-TO SAVE A DOCUMENT. (CTRL+S)

• PRINT-TO PRINT A DOCUMENT. (CTRL+P)

• PREPARE-TO PREPARE DOCUMENT FOR

DISTRIBUTION.SEND-TO SEND A COPY OF

DOCUMENT TO OTHER PEOPLE.PUBLISH-TO

DISTRIBUTE DOCUMENT TO OTHER

PEOPLE.CLOSE-TO CLOSE A DOCUMENT

OFFICE BUTTON CONTAINS

WORKING WITH CELLS TO COPY AND PASTE CONTENTS:

•

Select the cell or cells you wish to copy. Click the Copy command in

the Clipboard group on the Home tab. Select the cell or cells where you

want to paste the information. Click the Paste command. The copied

information will now appear in the new cells.

WORKING WITH CELLS TO CUT AND PASTE CELL CONTENTS:

Introduction to Spreadsheets

• Microsoft Excel is the spreadsheet program in Microsoft Office.

• _{A spreadsheet is a grid of rows and columns in which you enter text,} numbers, and the results of calculations.

Exploring the Parts of the Workbook

Each workbook contains three worksheets by default. The

worksheet displayed in the work area is the active

worksheet.

•

Columns

appear vertically and are identified by letters.

•

Rows

appear horizontally and are identified by numbers.

•

A

cell

is the intersection of a row and a column. Each cell is

identified by a unique

cell reference

. The cell in the

worksheet in which you can type data is called the

active

cell

.

•

The

Formula Bar

displays a formula when a worksheet cell

contains a calculated value. A

formula

is an equation that

calculates a new value from v

alues currently in a

Data Entry

There are two ways to enter information into a cell:

1. Type directly into the

cell.

Click on a cell, and type in

the data (numbers or text)

and press Enter.

2. Type into the formula

bar.

Formulas and Functions

•

Formulas are equations that perform

calculations in your spreadsheet. Formulas

always begin with an equals sign (=) or (+).

When you enter an equals sign or + into a cell,

you are basically telling Excel to “calculate

this.”

•

Functions are Excel-defined formulas. They

More on Functions

•

All functions have a common format – the equals sign

followed by the function name followed by the input in

parentheses.

•

The input for a function can be either:

–

A set of numbers (e.g., “=AVERAGE(22, 24,27, 21, 19)”)

•

This tells Excel to calculate the average of these

Functions for Descriptive Statistics

=AVERAGE(first cell:last cell): calculates the mean

=MEDIAN(first cell:last cell): calculates the median

=MODE(first cell:last cell): calculates the mode

=VAR

P

(first cell:last cell): calculates the variance

=STDEV

P

(first cell:last cell): calculates the standard deviation



You may directly write the functions for these statistics into

cells or the formula bar, OR



You may use the function wizard ( in the toolbar)

Below are several functions you will need to

Functions for Descriptive Statistics

A 10 10

B 12 13

C 15 18

D 18 20

E 10 15

F 9 11

G 11 12

H 8 6

I 13 9

J 6 8

Mean 11.2 12.2

Median 10.5 11.5

Mode 10 #N/A

Varianza 12.178 19.511

Standard deviation 3.490 4.417

•

Your Excel

Correlations and Scatterplots

The objective of the correlation is to measure the degree of association between the variables. The simple correlation analysis deals with the association between two or more variables, e.g. Price and demand, amount of rainfall and rice yield, etc.

Every correlation has a

direction

(direct = positive

or inverse = negative):

•

scores on another variable.

•

scores on the other variable.

Every correlation has a

magnitude

or

strength

:

Calculating Pearson’s

r

Hypothesis

If there is a relationship between number of hours spent per week studying for Inferential Statistics and final score earned in the class at the end of the quarter.

To do so, we can calculate Pearson’s r for these two variables. Enter the following data into Excel:

Student

Hours/

study Final score

1 8 15

2 14 17

3 7 13

4 10 15

5 5 10

6 3 10

7 1 7

8 12 18

9 5 12

10 3 7

Hours/Study

= average number

of hours spent per week studying

for Inferential Statistics

Final Score

= grade-point

average earned in Inferential

Find ‘r’ with Excel

Step 1: Select the cell where you want your r value to appear (you might want to label it).

Step 2: Click on the function wizard button. Step 3: Search for and select CORREL.

Step 4: For Array1, select all the values under Study Hrs.

Interpreting the correlation coefficient

What does the r value of .9475 = .95 tell you about the strength and direction of the correlation between Study/Hrs and Final score?

Direction, the answer is positive, therefore this is direct association Strength = .95 It is strong

Interpretation: There is a direct strong association between number of hours spent per week studying for Inferential Statistics and final score.

 Scatterplots and correlations are described:

 As positive or negative.

 As weak, moderate, or strong.  Using the r value..

 Then you want to describe the general relationship between the two

variables:

More hours of studying for Inferential Statistics was associated with a higher final score earned in the class at the end of the quarter.

 NOTE: We cannot say “More studying led to a higher final score” –

Scatterdots or Scatterplots

•

A scatterplot is an excellent way to visually

display the relationship (correlation) between

two variables.

•

Each point on the scatterplot represents an

individual’s data on the two variables.

•

We will now create a scatterplot for Study/Hrs

Content

Perform Simple Linear

Perform Simple Linear Regression

From a to z

You may want to organize or rearrange data in your

worksheet. To sort data in the worksheet, click the

Editing spreadsheets

•

To rename a worksheet:

– press ENTER

•

You can also Delete &

Selecting a Group of Cells

Preferences for leisure

activities in adults by gender

Dance Sports TV Total

Men 2 10 8 20

Women 16 6 8 30

Total 18 16 16 50

•

A group of selected cells is called a

range

. The range is

identified by its range reference, for example, A2:E4.

•

In an

adjacent range

, all cells touch each other and form a

rectangle.

Selecting a Group of Cells

Preferences for leisure

activities in adults by gender

Dance Sports TV Total

Men 2 10 8 20

Women 16 6 8 30

Changing Data in a Cell

• You can edit, replace, or clear data.

• You can edit cell data in the Formula Bar or in the cell. The contents of the active cell always appear in the Formula Bar.

• To replace cell data, select the cell, type new data, and press the Enter button on the Formula Bar or the Enter key or the Tab key.

Zooming a Worksheet

•

You can change the magnification

of a worksheet using the Zoom

controls on the status bar.

•

•

The default magnification for a

workbook is 100%.

•

For a closer view of a worksheet,

Zooming a Worksheet

cont…

Assigment

1. The advertising expenditure x and sales y in thousands of dollars for a small retail business in its first eight years in operation are shown in the table.

x 1.4 1.6 1.6 2 2 2.2 2.4 2.6

y 180 184 190 220 186 215 205 240

a. Compute the coefficient of correlation b. Draw the linear regression