DOC

opening excel and inputting data

By Henry Patterson,2014-11-10 05:42
9 views 0
opening excel and inputting data

Basic Overview of Excel

Table of Contents

Table of Contents ............................................................................................................ 1

    Introduction to Excel ....................................................................................................... 3

    Opening Excel and Inputting Data ................................................................................... 3 Opening a Document ................................................................................................... 3 Entering in Data .......................................................................................................... 4 Sorting and Organizing Data ........................................................................................... 4 Graphing Data ................................................................................................................. 4

    Scatter Plot .................................................................................................................. 5

    Adding Trendlines ....................................................................................................... 6 Formulas for +,-,*,/ of Cells ............................................................................................ 6 Adding/Subtracting Two Cells ..................................................................................... 6

    Adding/Subtracting Multiple Cells ........................................................................... 6 Dividing/Multiplying Individual Cells ......................................................................... 7 Basic Statistical Analysis ................................................................................................. 7 Average (Mean) of a Set of Data ................................................................................. 8 Median ........................................................................................................................ 8

    Quartile ....................................................................................................................... 8

    Standard Deviation ...................................................................................................... 9 t-Test ........................................................................................................................... 9

    Covariance ................................................................................................................ 10 Matrix (Array) Operations ............................................................................................. 11 Multiplying Matrices ................................................................................................. 11 Inverse Matrix ........................................................................................................... 12 Determinant Matrix ................................................................................................... 14 Pivot Tables .................................................................................................................. 15

    Using a Pivot Table to Tally Columns: ...................................................................... 15 Tallying Data by Two Categories .............................................................................. 16 Making a Graph Directly from the Pivot Table .......................................................... 16 Removing Pivot Table Rows or Columns .................................................................. 16 Using Pivot Tables to Display Calculated Data .......................................................... 16 Other Resources ............................................................................................................ 17

     1 11/10/2011

Basic Overview of Excel

2 11/10/2011

Basic Overview of Excel

Introduction to Excel

    Microsoft Excel helps you to organize, analyze and attractively present data.

    A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a

    grid made from columns and rows. It is an environment that can make number

    manipulation easy and somewhat painless.

    The math that goes on behind the scenes on the paper ledger can be overwhelming.

    If you change the loan amount, you will have to start the math all over again

    (from scratch). The nice thing about using a computer and spreadsheet is that you

    can experiment with numbers without having to RE-DO all the calculations.

    NO erasers! NO new formulas! NO calculators!

    Excel has many applications:

     Sorting and organizing data

     Creating visual representations of the data

     Addition, Subtraction, Division, Multiplication of Cells

     Statistical analysis

    o Average (Mean)

    o Median

    o Quartile

    o Standard deviation

    o t-Test

    o Covariance

     Matrix Operations

    o Addition/Subtraction

    o Multiplying

    o Inverse

    o Determinant

    Opening Excel and Inputting Data

     Opening a Document

    New Document: Start ? Programs ? Microsoft Office ? Excel

     Saved Document: ? File ? Open then select the document you would

     like to open (then click open)

     3 11/10/2011

    Basic Overview of Excel

     Entering in Data

    What you see on the screen is a new Excel Document. Each rectangle is a

    Cell which is arranged in rows and columns each having a name. The first

    cell (upper left-hand corner of the document) is A1, moving one cell to the

    right is B1, and moving down one from A1 is A2.

    To type in data (either number, words, formulas) simply click on a cell

    and begin typing. When finished you can either click on a new cell to

    enter more data. Or, to move one right press Tab and pressing Enter brings

    you back to the first column entered but one row down. The Arrow Keys

    will also move you from one cell to the next.

    If you can not see all your data on the screen simply select the Row or

    Column by clicking on the letter (A, B, C…) or number (1, 2, 3…) in gray

    and then go to

    Format ? Row ? Auto Fit Section

     Or

    Format ? Column ? Auto Fit Section

    Sorting and Organizing Data

     You may sort data in ascending, descending or alphabetical order.

    Highlight the data.

    Go to:

     Data;Sort

    A window will open with several options for sorting the data

    In the Sort by dialogue box, use the drop down menu to highlight the variable you

    want the data sorted by (2003 ELA Scaled Score for example). Click on

    Ascending or Descending.

    The data will sort by that category. In other words, if you sorted by Ascending

    2003 ELA Scaled Scores, the spreadsheet will now start with the lowest score and

    progress to the highest score, and the other columns will correspond to the

    appropriate score (i.e., all of the columns will not be sorted ascending; the

    integrity of the data will be maintained)

    Graphing Data

    You may create different types of graphs to visually represent your data. Perhaps

    the most pertinent type of graph you will be creating for this project is a scatter-

    plot. To make a scatter-plot

     4 11/10/2011

    Basic Overview of Excel

    Scatter Plot

    You may create different types of graphs to visually represent your data. Perhaps the most pertinent type of graph you will be creating for this project is a scatter-plot. To make a scatter-plot

Highlight the data you need for the scatter-plot (don’t worry about

    highlighting extra data)

Go to:

     Insert;Chart

    1. A window will appear asking what type of chart you would like to

    create Select XY (Scatter) from the menu on the left

    2. When you select the Scatter-plot option the right side of the

    window will change to show Chart Sub-Types. Select the first one

    (where Excel will not connect the points) and click Next >

    3. Click on the tab Series (top of window)

    4. To select the values to be used on the x-axis, click on the blue and

    red box to the right of the X Values blank. You will now see your

    original spreadsheet. Click and drag over the appropriate values.

    Then press Enter or click on the box on the right.

    5. To select the values for the y-axis, follow the same procedure as

    above except in the Y Values box.

    6. You should see a sample of your graph in the window (make sure

    it makes sense).

    Click Next >

    7. In the proceeding Chart Options box you may do a variety of

    things such as assign labels to the axes (under the Titles tab) or

    create a legend under the Legend tab

    8. Click Next > when you are done customizing the chart.

    9. The last dialogue box asks if you would like the chart placed on a

    separate sheet (literally, a separate page) or as an object on the

    same page. Select one of the two options and click Finish.

     5 11/10/2011

Basic Overview of Excel

    Adding Trendlines

    1. Click on your existing Chart

    2. Select Add Trendline

    Chart;Add Trendline

    3. Select an appropriate option from the Trend/Regression Type dialogue

    box (linear, exponential, polynomial, etc.)

    4. Click on the Options tab

    5. Check the boxes next to Display equation on chart and Display r -

    squared value on chart if not already checked (this will show the

    equation for the generated regression line)

    6. The trendline should now be displayed on the graph (you may double-

    click it to change properties like color)

    7. The equation for the line should also be displayed on the chart. Note

    that it may need to be dragged to an area of the chart where it is visible

    (it may be buried behind the actual data points)

    Formulas for +,-,*,/ of Cells

     Adding/Subtracting Two Cells

     Click on a empty cell (where you want the output) type in stnd =Name of 1 Cell + Name of 2 Cell

     Example

    A B C

    1 8 9 =A1+B2 17

    2 10 11

    3 7 6

    To add Column A and B for the other rows simply click on C1 so there is

    a black box around the cell then bring your curser to the lower right-hand

    corner when your curser turns in to a + sign, click, hold and drag the

    curser so it highlights C2 and C3 and release.

     Adding/Subtracting Multiple Cells

     Type in your two matrices

    Example

     6 11/10/2011

    Basic Overview of Excel

    A B C D E F G

    1 1 2 3 1 3 5

    2 4 5 6 2 4 6

    3 7 8 9 7 9 11

    4

    5

    6

    7

    Click on an empty cell and type in the addition equation for A1

    and E1

     Example

    A B C D E F G

    1 1 2 3 1 3 5

    2 4 5 6 2 4 6

    3 7 8 9 7 9 11

    4

    5 =A1+E1

    6

    7

    Highlight A5 and click on the bottom right-hand corner and drag it

    to the right two and down two. When you let go you will see:

    A B C D E F G

    1 1 2 3 1 3 5

    2 4 5 6 2 4 6

    3 7 8 9 7 9 11

    4

    5 2 5 8

    6 6 9 12

    7 14 17 20

    Dividing/Multiplying Individual Cells

     Is done the same as adding/subtracting cells

    Basic Statistical Analysis

     Excel can compute a variety of basic statistics about a set of data.

     7 11/10/2011

Basic Overview of Excel

     Average (Mean) of a Set of Data

    1. Click the cell where you would like the mean to be displayed

    2. In the formula bar at the top of the document, type:

     =AVERAGE(starting cell:ending cell),

    Example:

    =AVERAGE(D2:D357)

    (this would compute the average of the set of data starting

    in cell D2 and ending in D357)

     Or

    You may type =AVERAGE(then highlight the set of cells

    you want the average of)

     Median

    1. Click the cell where you would like the median to be displayed

    2. In the formula bar at the top of the document, type:

    =MEDIAN(starting cell: ending cell),

    Example:

     =MEDIAN(D2:D357)

     Or

    You may type =MEDIAN(then highlight the set of cells

    you want the average of)

    Note: If there is an even number of numbers in the set, then MEDIAN

     calculates the average of the two numbers in the middle

     If an array or reference argument contains text, logical values or

    empty cells, those values are ignored; however, cells with the value

    zero are included

     Quartile

    1. Click the cell where you would like the quartile to be displayed

    2. In the formula bar at the top of the document type:

    =QUARTILE(starting cell: ending cell, quart)

    If quart equals QUARTILE returns

     8 11/10/2011

Basic Overview of Excel

    0 Minimum value

    1 First quartile (25th percentile)

    2 Median value (50th percentile)

    3 Third quartile (75th percentile)

    4 Maximum value

    Standard Deviation

    1. Click the cell where you would like the standard deviation to be

    displayed

    2. In the formula bar at the top of the document type:

    =STDEV(starting cell: ending cell),

    Example:

     =STDEV(D2:D357)

     Or

    You may type =STDEV then simply highlight the relevant

    cells

    NOTE: Excel uses the following formula to compute STDEV:

    2xx;; (using the “unbiased” or n-1 method) (n1)

     If you want the standard error calculation to not be based on (n-1) and

     simply have n in the denominator, use the STDEVP function (input

     syntax same as STDEV)

     t-Test

    1. Click the cell where you would like the t-test to be displayed

    2. In the formula bar at the top of the document type:

     = TTEST(array1,array2,tails,type)

     Where:

     Array 1 is the first data set (Selected by highlighting)

    Array 2 is the second data set (Selected by highlighting)

     Tails specifies the number of distribution tails (1 or 2)

     Type is the kind of t-Test to perform

     9 11/10/2011

    Basic Overview of Excel

    If type equals This test is performed

    1 Paired

    2 Two-sample equal variance (homoscedastic)

    3 Two-sample unequal variance (heteroscedastic)

Covariance

    Covariance can be calculated easily and on a large scale using the

    capabilities of an Excel spreadsheet. First, create a chart to compare the

    different stocks:

Covariance Matrix

     ADM IBM KO MAY XOM

    ADM

    ? IBM

    KO

    MAY

    XOM

    For example, the ? will represent the calculated covariance of KO and

    IBM stocks, or the degree to which their rates of return move together

    over the investigated period.

    Next, the covariance must be calculated for each cell in the table.

     As an example, for cell ?, KO vs. IBM, follow the steps below.

    1. Select the cell

    2. Click the function (f) button x

    3. Select the function COVAR, click OK

    4. A box will appear into which you must enter two

    arrays

    5. For Array 1, click the button at the end of the text

    field, then choose the entire column of daily returns

    for KO, rows 5-255. Press Enter.

    6. For Array 2, use the same process to select the

    entire column of IBM daily returns. Press Enter

    7. Click OK

    Repeat this process for each cell. The covariance function is communitive,

    and therefore it does not matter which order the arrays are selected in.

     10 11/10/2011

Report this document

For any questions or suggestions please email
cust-service@docsford.com