Other Resources

"Man ultimately decides for himself! And in the end, education must be education towards the ability to decide" - Victor Frankl

 

Using Formulas

Excel uses formulas to perform calculations in a worksheet. Before you begin entering the formulas, you need to know how formulas work and how to write them correctly in Excel.

In Excel, you always begin the formula with an equal sign. For example the equation 3+2=5 would be entered into Excel as =3+2.

Although you can enter formulas in Excel using numeric values, most formulas make extensive use of cell references. A cell reference is the address of a specific cell or group of adjacent cells (called a range). When a formula uses a cell reference, it calculates using the values in the cell(s) referenced, allowing you to easily change the calculations by changing the value in the referenced cell.

A formula can contain any or all of the following: functions, cell references, arithmetical operators, and constants (values that do not change). The arithmetical operators that you can use are:

Operator Meaning Example Results (if A1=20 and A2=2)
+ Addition =A1+A2 22
- Subtraction =A1-A2 18
* Multiplication =A1*A2 40
/ Division =A1/A2 10
% Percentage >=A1% .2
^ Exponentiation =A1^A2 400

You can enter a formula in a cell by typing directly in the cell or by using the pointing method. In this exercise we will use the following business formulas:

When calculating the results of a formula hat has multiple operations, Excel follows a set of mathematical rules called order of precedence in which certain operations are performed before others. The order is as follows:


Entering Formulas

formula bar

The typing method for entering formulas

  1. Select cell D5 and type =c5-b5
  2. Press [Enter]. Cell D6 will become the active cell

The point method for entering formulas

  1. Type an equal sign (=) and then press [ <-] to point to cell C6.
  2. Type a subtraction sign (-) and then press [ <-] twice. The formula will change to =C6-B6.
  3. Press [Enter]. The formula is entered in cell D6 and cell D7 becomes the active cell.
  4. Click cell F5, type =e5*c5, and then press [Enter]. The formula to multiply the projected units times the price is entered in cell F5 and cell F6 becomes the active cell.
  5. Click cell G5 and type an equal sign (=)
  6. Click cell E5 and then type an asterisk (*).
  7. Click cell D5 and press [Enter]. The formula is entered in cell G5 and G6 become the active cell.
  8. Using any method, enter the appropriate formula in cell H5.

Copying Formulas

Once a formula has been entered, it can be copied to other worksheet cells by using the Copy and Paste commands or by using the Fill Series feature. When Excel copies a formula that contains cell references, it automatically changes the cell references to the correct cells in relationship to the new location of the formula.

copy cell function

Paste Option

When you paste in Excel, the Paste Options button displays at the bottom right corner of the new text. This button has a drop-down arrow that displays a menu of options related to formatting the pasted data.

  1. Click cell D5 and then click the [Copy] button.
  2. Drag the mouse from cell D7 to D9. The cells are selected.
  3. Click the [Paste] button. The range is filled with copies of the formula

formula change function

Fill Option

  1. Click cell F5 and point to the fill handle – the small rectangle in the lower- right corner of the cell. The mouse pointer will change to a small black plus sign.
  2. Drag the fill handle to cell F9. The range is filled with copies of the formula.
  3. Using the fill handle, copy the formula in cell G5 to cells G6 through G9.

Using either method, copy the formula in cell H5 to cells H6 through H9. Save your changes.

Using Functions

A function is a predefined formula that uses specific values, called arguments, which are arranged in a specific syntax. Because a function is a formula, it always begins with an equal sign. Next comes the name of the function followed by the arguments, which are enclosed in parentheses.

Just as with formulas, there are various methods for entering a function. You can type the entire function, use the Insert Function dialog box, or type part of the function and use the pointer to enter cell references.

To denote a range in a function, separate the first and last cell in the range with a colon. The order in which the cells are listed does not matter. For example A1:A10 and A10:A1 note the same range.

Typing the function in directly

  1. Select cell E10
  2. Type =sum(e5:e9) and press [Tab] to enter the formula and move to the next cell in the row.

insert function

Using the Insert Function dialog box

  1. Click the Insert Function [fx] button on the Formula bar to open the Insert Function dialog box. Notice that Excel automatically inserts the equal sign in the cell.
  2. Select SUM, if necessary and click [OK].
  3. Click [OK].

By default the AutoSum enters the SUM function automatically with a cell range basted on the design of your worksheet, the button has a drop-down list for other functions too. If the AutoSum button enters an incorrect cell range, just click and drag the correct range.

function arguments

Note: If you are not certain what the name of a function is, you can search for it by typing a brief description of what the function does in the Search for a Function text box and clicking Go. The functions that match your description are displayed in the Select a function list.

auto sum button

Using the Pointer for Cell Reference

  1. Select cell G10. Type =Sum( and them click cell G5. Drag to cell G9.
  2. Press the [Tab] key. Excel enters the final parenthesis for you and moves to cell H10.
  3. Click the AutoSum button on the Standard toolbar and press [Enter].
  4. Select the cell range E10:H10, press [Delete] and then click the AutoSum button on the Standard toolbar.
  5. Save your changes.

Entering a Formula with an Absolute Cell Reference

Absolute cell references are used when you need to prevent the row or column numbers from changing when they are copied. A dollar sign $ entered before a column letter or row number identifies it as an absolute reference.

formula error button

Entering a Formula with an Absolute Cell Reference

  1. Click cell I5, type the formula =f5/f10, and press [Enter]
  2. Copy the formula in cell I5 into cell I6 and down to cell I9.
  3. Click the cell I6 and point to the Formula Error button.
  4. Click the drop down button. The menu tells you there is a Divide by Zero Error.
  5. Press [Esc].
  6. Double-click cell I5.
  7. Select the reference F10, and press the [F4] key. (The other way to insert the dollar signs for an absolute cell address is to type the dollar signs.)
  8. Press the [Enter] key or click the [Enter] button on the Formula bar.
  9. Use the fill handle to copy the formula down to cell I10.
  10. Save your changes.

Inserting a Row

If you insert a new row in the middle of an existing range Excel will adjust the SUM functions automatically. For this next exercise lets pretend that we have been presented with additional data that will require that we add a new product to the product line.

short cut menu

  1. Right-click the row header for row 7 to display the shortcut menu.
  2. Choose Insert from the shortcut menu.
  3. Type the following data into the associated fields: Just for Grandma basket; 10.95; 42.95; 32; 300.
  4. Select cell D6 and use the fill handle to copy the formula to D7.
  5. Select the range F6:I6.
  6. Copy the cell range down one row using the fill handle.

copy cell range

  1. Select cell E11 and notice that the formula automatically changes from =SUM(E5:E9) to =SUM(E5:E10) to accommodate the newly inserted row.
  2. Click the Spelling button to check the spelling of the worksheet.
  3. Save your changes and close the file.

Functions:

Functions are built-in formulas with names indicating their purpose.  For example, NETWORKDAYS, is a function with a built-in formula to calculate the number of real workdays between two dates.

Functions are separated into categories. Click on the Function Wizard icon auto sum button, then Click on …More Functions to bring up this window.

insert function

The Help feature provides description, purpose and syntax for the function.

Scenario: You are given a spreadsheet with the Last Name and First Name in separate columns and for some reason you would like them together in one column, listed a lastname, firstname.

  1. Open the workbook “Excel Workshop Files”
  2. Click on the tab, “Names&Amounts”
  3. Insert a Column between B and C

To combine two columns of data into one, use the CONCATENATE formula, which can be found using the Function Wizard  auto sum button under the Text category OR type in the formula using the ampersand (&):

=B2&”, “&A2

function wizard

  1. Then copy the formula down the column.

The result of using that function is a column with Last Name, First Name.

spreadsheet sample

Next you are going to use the function to count the number of people whose Amount is greater than 2000. There is a function, COUNTIF, to perform that count based on the criteria >1999 (if we use 2000 then the actual number will not be counted because 2000 is not greater that 2000. We could also state it as greater than OR equal to 2000)

  1. Click in cell D19
  2. Click on the Function Wizard icon auto sum button, …More Functions.
  3. Choose the All category, find COUNTIF from the alphabetical listing.

insert function screen

  1. Click OK
  2. In the next window, enter the RANGE you want to evaluate and enter the formula for the criteria.

function arguments

  1. Click OK
  2. In this case the result is 4 - which represent the number with a value of 2000 or greater.
  3. Follow these steps to total the actual amount value for those 4.
  4. Use D20 for this result, and use the Function SUMIF.

function arguments range

Note: You may have to add the additional functions. Do this by clicking on the Function Wizard function wizard button, choose Add-Ins:

add ins screen


Data Analysis Tools

Excel provides a variety of ways to obtain summary measures for a set of data. The three major ones are: Data Analysis Tools, Function Wizard, and Pivot Table.

Data Analysis is located in the Tools menu. The Descriptive Statistics Tool provides a quick and simple way of obtaining, simultaneously, several descriptive measures for a single variable or for two or more variables. The Descriptive Statistics Tool has one disadvantage, however – and that is that there can be no missing data. We recommend that you use the Function Wizard or the Pivot Table to obtain descriptive statistics for data sets that contain missing observations.

Note: If Data Analysis is not listed in the Tools menu, then you will need to add it. To do this, simply click on Tools and then click on Add-Ins in the Tools menu. Click the box next to Analysis ToolPak so that a check mark appears in the box. Then click [OK]

Descriptive Statistics

Scenario: Student Services wanted to gather information regarding the computer experience of incoming freshmen. They conducted an extensive survey that included many questions, two of which were the number of years that they have used computers and how many computers are in their homes. We will use the response provided by 15 of the incoming freshmen to conduct a sample analysis.

  1. Open the Excel Workbook titled  “Excel Workshop Files”
  2. Click on the tab “Descriptive”

tools dropdown menu

  1. Click on Tools and then click on Data Analysis in the Tools menu. The Data Analysis dialog box will be displayed.

data analysis screen

  1. Click on the Descriptive Statistics line. Then, click [OK].

discriptive statistics screen

  1. In the Input Range field enter the worksheet location of the data you wish to analyze (A3:A18)
  2. Click on the “Labels in First Row” box
  3. Select the output option. There are three options available:
    1. Output range – this option places the output range in the same worksheet as the data
    2. New Worksheet ply – This is the default option. This option pastes the output in a new worksheet.
    3. New Workbook – select a New Workbook if you want to create a new workbook to display the analysis output.

For this exercise select the cell on the current worksheet where you would like that data placed.

  1. Click the box next to “Summary statistics”
  2. Click [OK]. The output will be displayed in the selected cells.

descriptive statisitics results

  1. Repeat the above listed steps for the computer results.