Opening a Blank Excel Sheet

Excel Basics

Opening a Blank Excel Sheet

1. Look for the Excel icon on the computer screen (it looks like a green X)

2. If you can’t find it, click Start, then Programs, then Microsoft Office, then Excel

Using Excel: Basic Arithmetic

Any computation in Excel must start with an equal sign.The following symbols are used in Excel:

symbol / meaning / Example / output
+ / Add / =3+8 / 11
- / Subtract / =4-9 / -5
* / Multiply / =7*3 / 21
/ / Divide / =24/50 / 0.48
^ / Exponent / =2^3 / 8
SQRT / Square root / =SQRT(9) / 3
( ) / Parentheses / =(9+1)/2 / 5

Each rectangle on the spreadsheet is called a cell. Each cell has an address, which is a letter (which indicates the column) followed by a number (which indicates the row). For example, cell B17 means the cell in column B, row 17. A group of cells is called an array.

When doing arithmetic, you can refer to the number in a particular cell. For example:

A / B / C / D
1 / First test / 78
2 / Second test / 67
3 / Average / =(B1+B2)/2
4
5

Using Excel: Basic Statistical Commands

The following commands are used in Excel:

COUNT – gives the number of numerical observations in an array

SUM – gives the sum (the total) of the observations in an array

MIN – gives the minimum number of an array

QUARTILE – gives the 1st, 2nd, or 3rd quartile of an array

MEDIAN – gives the median of an array

MAX – gives the maximum number of an array

AVERAGE – gives the mean of an array

STDEV – gives the standard deviation of an array

VAR - gives the variance of an array

FREQUENCY – counts how many observations are less or equal to a certain number

Anytime you enter a command in Excel, type an equal sign ( = ) first. This tells Excel you are entering a command.

Example A:

1. Always label your data. In cell A1, type “Raw Data”. Then type the following numbers in cells A2 through A11:

A
1 / Raw data
2 / 34
3 / 42
4 / 51
5 / 17
6 / 52
7 / 46
8 / 52
9 / 53
10 / 60
11 / 25

This list of numbers is called an array. (When referring to this list in Excel, type A2:A11, which means cell A2 through cell A11. The colon means “through”)

2. Next we will find the 5-number summary, mean, and standard deviation.Next to each computation, type an appropriate label:

In a blank cell, type =SUM(A2:A11). This will find the total of all the numbers in cells A2 through A11.

In a blank cell, type =COUNT(A2:A11). This will give the number of numerical observations in the array.

In a blank cell, type =MIN(A2:A11). This will compute the minimum number in the cells A2 through A11.

In another blank cell, type =QUARTILE(A2:A11,1). This will compute the first quartile.

In another blank cell, type =MEDIAN(A2:A11). This will compute the median.

In another blank cell, type =QUARTILE(A2:A11,3). This will compute the third quartile.

In another blank cell, type =MAX(A2:A11). This will compute the maximum.

In another blank cell, type =AVERAGE(A2:A11). This will compute the mean.

In another blank cell, type =STDEV(A2:A11). This will compute the standard deviation.

When you are finished, the spreadsheet should look something like this:

A / B / C / D
1 / Raw data /
2 / 34 / 432 / sum
3 / 42 / 10 / count
4 / 51 / 17 / min
5 / 17 / 36 / Q1
6 / 52 / 48.5 / median
7 / 46 / 52 / Q3
8 / 52 / 60 / max
9 / 53
10 / 60 / 43.2 / mean
11 / 25 / 13.7663 / sd

Example B: Suppose you want to know how many observations are 34 or less

1. In a blank cell, type =FREQUENCY(A2:A11,34)

The answer is 3 (because the numbers 17, 25, and 34 are less than or equal to 34)

Example C: Suppose you want to know how many numbers are between 50 and 60 (including 60 but not 50) (This is useful for making frequency distributions)

Use the FREQUENCYcommand twice. First find how many numbers are less than or equal to 60, then find how many numbers are less than or equal to 50, then subtract.

1. In a blank cell, type =FREQUENCY(A2:A11,60). The answer should be 10.

2. In another blank cell, type =FREQUENCY(A2:A11,50). The answer should be 5.

3. Subtract the two numbers: 10 - 5 = 5

Using Excel: The Function Wizard

The top of the Excel sheet has many useful buttons (this is called the toolbar) Look for the button that shows

This activates the Function Wizard, which lists every command available in Excel. This is good to use when you forget the exact syntax of a particular command. If you can’t find the button, you can select Insert, then function from the menu.

Example A: Use the function wizard to find the 3rd quartile of the data in cells A2:A11.

Click the function wizard button: fx
On the window that pops up, select the statistical category. Then select QUARTILE.
A new window pops up, and fill in the blanks:

Array: A2:A11

Quart:3

Using Excel: Sorting

Another button on the toolbar looks like this:

A / |
Z / |

This sorts the data from least to greatest. If you can’t find the button, go to Data, then Sort on the menu.

Example A: Sort the following raw data from least to greatest.

A
1 / Raw data
2 / 34
3 / 42
4 / 51
5 / 17
6 / 52
7 / 46
8 / 52
9 / 53
10 / 60
11 / 25

Use your mouse to select the data, including the column heading
Click the “Sort” button.

Now the spreadsheet will look like this:

A
1 / Raw data
2 / 17
3 / 25
4 / 34
5 / 42
6 / 46
7 / 51
8 / 52
9 / 52
10 / 53
11 / 60

If you can’t find the “sort” button, go to the menu “data” then “sort”.

Normal Distribution (z-score) Problems on Excel

There are two useful commands: NORMDIST (short for Normal Distribution), and NORMINV (Short for Normal Inverse).

NORMDIST gives the percentage (as a decimal) of individuals (assuming a normal distribution) whose measurements are less than or equal to a particular value.

Example A: The heights of women is approximately normal with mean = 64.5 inches and standard deviation = 2.5 inches. What proportion of women are taller than 68 inches?

Solution

Select a blank cell. Click the Function Wizard (fx), then select NORMDIST from the Statistics category.
A window pops up, and fill in the blanks:

x:68 (the measurement you are interested in)

Mean: 64.5

Standard_Dev: 2.5

Cumulative: TRUE (this tells the computer that you want the percentage of individuals that measure 64.5 OR LESS)

Click OK
The answer gives the percentage of women whose height is less than or equal to 68: 0.9192
In another cell, subtract from 1 to get the percentage of women taller than 68: 0.0808

Answer: 8.08%

NORMINV gives the measurement below which a certain percentage lies.

Example B: The heights of women is approximately normal with mean = 64.5 inches andstandard deviation = 2.5 inches. The shortest 15% of the women are shorter than what height?

Solution

Select NORMINV in the function wizard
A window pops up, and fill in the blanks:

Probability:0.15 (this is the percentage)

Mean: 64.5

Standard_Dev: 2.5

Click OK
Answer: 61.9