LearnShop Tutorial on Using Excel 2007 with Business Statistics

 

Learning Objectives

Upon completion of this learning session you should be able to:

  • Identify parts of the Excel 2007 window
  • Navigate through an Excel Worksheet
  • Build basic mathematical formulas with Excel
  • Insert Business Functions for statistical work with Excel
  • Find supporting documentation when working with Excel Functions

 

Starting Microsoft Excel from the Desktop

Click Here
 

 

The Office 2007 redesign includes some radical new interface elements aimed at making it easier to find the features you need and execute tasks more efficiently. The most important of these new elements is the Ribbon, which offers task-oriented tabs with commands readily available instead of hiding in a menu structure.

 

 

 

 

 

The following picture shows parts of the Excel 2007 window.

Excell Buttons2

 

·   Office Button - provides access to workbook-level features and program settings.

·   Quick Access Toolbar - is a collection of buttons that provide one-click access to commonly used commands, such as Save, Undo, and Repeat.

·   Title bar - displays the name of the active workbook and the Excel program name.

·   Ribbon – main set of commands organized by task into tabs and groups.

·   Column headings – letters that appear along the top of the worksheet window to identify the different columns in the worksheet.

·   Workbook window – a window that displays an Excel workbook.

·   Vertical scroll bar – a scroll bar used to scroll vertically through the workbook window.

·   Horizontal scroll bar – a scroll bar used to scroll horizontally through the workbook window.

·   Zoom controls – controls for magnifying and shrinking the content displayed in the active workbook window.

·   View shortcuts – buttons used to change how the worksheet content is displayed – Normal, Page Layout, or Page Break Preview view.

·   Sheet tabs – tabs that display the names of the worksheets in the workbook

·   Sheet tabs scrolling buttons – Buttons to scroll the list of sheet tabs in the workbook

·   Row headings – The numbers that appear along the left of the worksheet window to identify the different rows in the worksheet

·   Select All button – a button used to select all of the cells in the active worksheet

·   Active cell – the cell currently selected in the active worksheet

·   Name box – a box that displays the cell reference of the active cell

·   Formula bar – a bar that displays the value or formula entered in the active cell.

 

 

 

 

 

 

 

 

An Excel Worksheet is made up of rows and columns. The intersection of the rows and columns creates cells having unique names consisting of the row and column identifiers.

Column A
 

Row 1
 

Cell A1
 
                                

 

To navigate to various cells, you can point and click with the mouse or use the keyboard arrow keys, along with other keyboard shortcuts.

 

 

 

 

 

 

 

 

 

To perform basic arithmetic with Excel, begin with an empty cell and insert an equals sign (=).  Then reference the cells to be included in the formula and add the appropriate operator(s).  The basic math operators are:

+   Addition

-   Subtraction

*   Multiplication

/    Division

^   Exponent

 

 

Below is an example of a multiplication operation.

 

 

 

Below is an example of using an exponential operation.

 

 

 

 

 

Finding Business Statistical Functions in Excel

 

Select the cell you want the function to be in, and then click the Insert Function button.

 

 

Click the Insert Functions

Next to the Formula Bar
 

 

 

Alternatively, you may click the Formulas tab, and click Insert Function as shown.

 

 


 

 

 

 

 

 

 

 

 

The Insert Function Dialog box will appear.

 

 

 

Excel has over 300 functions available.  Let’s look at examples of the PMT and FV functions. The National Bank makes a profit by taking money deposited by customers and lending it to others at a higher rate. In order to determine a borrower’s payment we can use the PMT function.  To determine the future value of a depositor’s account we can use the FV function.

 

PMT (payment) displays the payment (PMT) per period needed to repay a loan (Pv = present value) at a specified interest rate (Rate) for a specified number of periods (Nper).

            EX: PMT(Rate,Nper, Pv)

 

FV (future value) displays the future value (FV) of a series of equal payments at a fixed rate (Rate) for a specified number of periods (Nper).

EX: FV(Rate, Nper, Pmt)

 

 

 

 

 

 

 

 

Example 1: A potential borrower inquires about the payments on a $5,500 loan for four years. The current lending rate is 11%. Determine the yearly payment on the loan.

(Note: Payments are shown as negative amounts.)

 

 

 

 

 

 

 

 

 

 

 

Example 2: A potential depositor is starting a college fund for her son. She inquires about the value of yearly deposits of $450 at the end of 15 years. The current interest rate is 7.5%. (Remember to enter the deposit as a negative because it is an amount that must be paid.)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Functions provide an efficient way to have Excel perform complex calculations in specialized areas such as mathematics, statistics, logic, trigonometry, accounting, and finance. Click the Category dropdown button to see the available categories of functions.

 

Below is a partial look at Excel’s available Statistical Functions:

Statistical Category Selected
 

Dropdown Button
 

 

 

Here is how to insert a function which will calculate the standard deviation for an entire population of numeric values.  The standard deviation of the cells below can be found with the STDEVP function.  Start by selecting the appropriate cell for the function.

 

 

 

Next, open the Insert Function dialog box and select the function.

 

Function Description
 

Selected Function
 

Additional information on the function is found by clicking here
 
 

 

 

 


Appropriate cell selected and appropriate function selected. Now click OK. 

 

Insert the range of cells to include in the standard deviation calculation.  The semi-colon indicates that all the cells starting with D1 and ending with D10 will be included in the calculation. Excel may insert this range for you automatically.

If the label next to the textbox is bold, the textbox must have cell references or values inserted. If the label is not bold, cell references or values are optional. 

 

For this calculation, only the upper textbox must specify the range of cells to calculate. After the required textbox entry is complete, click the OK button to finalize inserting the function.

 

Completes Entry
 

Optional (not required for this example)

 
 

Bold (Required)
 

 

 

 

After clicking OK, the standard deviation calculation is complete.

 

 

 

 

To find out more about Excel functions, use Excel’s Help features to see additional function information.

 

Click ? for Help
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enter the topic of interest
 

 

 

Make sure you save your excel document on your personal storage media. There is a choice to save your document as “Excel 97 – 2003 Workbook.”

This concludes our Microsoft Excel 2007 Basics tutorial. You may call Information Commons at 623-935-8150 or send an email to rodica.heinz@estrellamountain.edu for any questions you have on this tutorial.