Using Microsoft Excel with Business Research Methods

By

William G. Zikmund

TITLE BARMENU BAR

FORMULA BAR

STANDARD TOOLBAR

FORMATTING TOOLBAR

ACTIVE CELL

PASTE FUNCTION

TOOLS MENU

The Paste Function Provides Numerous Statistical Operations

The Statistical Function Category

Data Analysis Dialog Box

• Click on “Tools”

• Select “Data Analysis”

• Select statistical operation – such as Histogram

Functions

• Functions are predefined formulas for mathematical operations

• They perform calculations by using specific values, called arguments

• Arguments indicate data or a range of cells

• Arguments are performed, in a particular order, called the syntax.

Functions

• Functions are predefined formulas for mathematical operations

• They perform calculations by using specific values, called arguments

• Arguments are performed, in a particular order, called the syntax.

• For example, the SUM function adds values or ranges of cells

Easy to Use Paste Functions

• AVERAGE (MEAN)

• MEDIAN

• MODE

• SUM

• STANDARD DEVIATION

Functions

• The syntax of a function begins with the function name

• followed by an opening parenthesis

• the arguments for the function

• separated by commas

• a closing parenthesis.

• If the function starts a formula, an equal sign (=) is typed before the function name.

The Equal Sign Then The Function Name And Arguments

• =FUNCTION (Argument1)

• =FUNCTION (Argument1,Argument2)

Arguments

• Typical arguments are numbers, text, arrays, and cell references.

• Arguments can also be constants, formulas, or other functions.

The AVERAGE Function Located in the Statistical Category

Data Array

• The data appear in cells A2 through 14

• A2:A14

• Sometimes written with dollars signs

• $A$2:$A$14

Sum, Average, and Standard Deviation

• =FUNCTION (Argument1)

• =SUM(A2:A9)

• =AVERAGE(A2:A9)

• =STDEVA(A2:A9)

SUM FunctionSales Call Example

)9:2( AASUMX

AVERAGE (Mean) FunctionSales Call Example

)9:2( AAAVERAGEn

XX

i

Standard Deviation FunctionSales Call Example

)9:2(

1

2AASTDEVA

n

xxxs

Proportion

• =COUNT

• =COUNTIF

• DIVIDE COUNTIF BY COUNT

• =D3/D2

Frequency Distributions

• There are alternative ways of constructing frequency distributions

• COUNTIF function

• HISTOGRAM function

=COUNTIF(A6:A134,1)=D4/D9*100

Histogram Function

• Tools -Data Analysis-Histogram

• Bins

The bins are thefrequency categories

Insert Input and Bin Ranges

Text Labels Can Be Included or Excluded From Input Range

The Chart Wizard

The Descriptive Statistics Function

SEVERAL ROWS OF DATA ARE HIDDEN

SEVERAL ROWS OF DATA ARE HIDDEN

Correlation

Correlation Coefficient, r = .75

Correlation: Player Salary and Ticket Price

-20-10

0102030

1995 1996 1997 1998 1999 2000 2001

Change in TicketPrice

Change inPlayer Salary

Regression Analysis