WyzGuys Computer Tutors

 Computer Instruction. Web Design Instruction,  and Web Hosting 

 

Using Formulas

 

Site Navigation

Introduction
Register
Resources
Excel Window
Toolbars
Create A Workbook
Save A Workbook
Open A Workbook
The Active Cell
Entering Data
Editing Data
Formatting Cells
More Formatting
Worksheets
Move & Copy Data
Fill a Range
Formulas
AutoSum
Functions
Copy Formulas
Print Preview
Page Orientation
Margins
Headers & Footers
Titles & Gridlines
Printing
Sorting Data
Charting
Modify Charts

 

Formulas

Excel can perform a wide variety of calculations using formulas. 

Formulas can be stored in worksheet and will perform calculations as constant values are entered into the worksheet.

 

As constant values change in a worksheet, formulas automatically recalculate results. 

 

Formulas can be created in one part of workbook and copied to other parts of the workbook.  Excel will automatically adjust the copied formulas so they work in the new locations.

 

To create simple formulas:

Select the cell that will display the results of the formula’s calculation.

  1. In the formula bar, type an equal = sign.  If you forget the = sign, Excel will not recognize the formula.

  2. Type the first cell address that contains a value to be calculated by the formula.  Always use cell addresses, not the values in the cells, when creating formulas.

  3. Type the arithmetic operator to be used in the formula.  Basic arithmetic operators for Excel formulas are:

    1. Multiplication      *

    2. Division             /

    3. Addition             +

    4. Subtraction         -

    5. Equal to             =

  4. Continue typing cell addresses and operators until the formula is complete.  (See the next page for operations order information.)

  5. Accept the formula by pressing Enter.

 

Order of Operations

Excel will perform calculations using mathematical order of precedence rules.  If several operators are combined in a formula, Excel performs the operations in the order shown in the following table, reprinted from Excel Help.  If a formula contains operators with the same precedence, Excel performs the operations from left to right.  To be sure your formula calculates correctly, enclose in parentheses the cell addresses and operators you want calculated first.

Operator

Description

( )

Parentheses

–

Negation (as in –1)

%

Percent

^

Exponentiation

* and /

Multiplication and division

+ and –

Addition and subtraction

&

Connects two strings of text (concatenation)

= < > <= >= <>

Comparison

 

For example:

The formula =5+10/2 will yield a result of 10 because Excel will perform the division operation first.

The formula =(5+10)/2 will yield a result of 7.5 because the addition operation enclosed in parentheses will be performed first.

 

Formulas and Decimal Place Formatting

In Excel it is possible to apply formats to cells that will increase or decrease the number of decimal places displayed.  For example, the value 1.6 can be entered into a cell.  If that cell is formatted to display 0 decimal places, Excel will display the value in the cell as 2.  However, the actual value stored in the cell remains 1.6.  (Actual value of the cell will be displayed in the formula bar when the cell is selected.)  If that cell is referenced in a formula, Excel will perform the calculation using the cell’s actual value (1.6) not the formatted value (2).

 

Formulas with Cell References and Functions

The most common use of formulas in Excel include cell references, not just numerical equations.  Many Excel formulas all contain FUNCTIONS, which are the capitalized letters behind the equal size.  Functions tell the formula how to process the cell range data.  Some common examples:

=C2 Displays the value of C2 in the selected cell.
=Sheet2!B2 Displays the value of cell B2 on Sheet2.
=SUM(A:A) Totals all values in Column A.
=SUM(A2:A34) Totals all the values of cells between A2 and A34.
=AVERAGE(A2:A34) Displays the average of all values in the cell range.

 

Back To Exercise 14

Next

Curriculum developed by WyzGuys Computer Tutors

All Rights Reserved - updated 12/07/2006

Hosted by WyzHost.com

contact support@wyzhost.com