Microsoft Excel Whiz 2002 2003

Lesson 1: Using Formulas

One of the most valuable features of Excel is its ability to store mathematical formulas in individual cells. A formula is a data entry capable of performing mathematical and logical operations automatically.

There are several things we need to consider before entering a formula.

  1. Determine first where to put the formula. The location of the formula must be in the cell where you want to see the results of the computations.

  2. Formulas in Excel begins with an equal (=) sign.

In the example above, the formula was written in the manner of writing it ordinarily. The only difference is that the = (equal sign) is in the beginning of the formula.

In MS Excel, instead of the value, the cell address of the value is usually used in writing the formula. In this way, any changes you make on the value will also take effect on the results of the formula.

A formula can also contain any or all of the following: functions, references, operators, and constants.

Note

It is better to use cell references instead of numbers when you build Excel formulas because it automatically calculates a new answer whenever you change a value that is referenced to a formula.

Working within a Cell or using the Formula Bar

In creating a working formula that can produce accurate results and outcome, one must know the rule of hierarchy in mathematical operators followed generally by your computer system. There are several operators like addition, subtraction, multiplication, and division. There is no problem when you are just using one operator at a time; but the difficulty begins when you put several operators together in a single formula. With more than one operators present in a formula, the logical question is which among the operators will the computer perform first?

To give you a clear picture, consider the following instructions;

Which among the two equations was correctly computed?

Equation 1

Equation 2

5*2-4/2=3

5*2-4/2=8

Both are identical equations, but they can have different outputs depending on your approach to them. Equation 1 was performed by multiplying 5 and 2 then subtracting 4 from its product and finally dividing the difference by 2, thus we have 3 as the final answer.

On the other hand, Equation 2 was computed by multiplying 5 and 2 first, then going to the right part of the equation by dividing 4 and 2. Then the quotient from the second part was subtracted from the product of the first part, giving as 8 as the final result. Which one of the approach is correct? Or which one will the computer use as a means of calculating your formula?

There is no problem at all once you have familiarized yourself with the order of mathematical operators in Excel. The following operators will be calculated first in subsequent order:

  1. Operators enclosed in a parenthesis [( )]

  2. Percentage [%]

  3. Exponential [⁁]

  4. Multiplication [*] and Division [/]

  5. Addition [+] and Subtraction [−]

  6. Concatenation (of strings) [&]

  7. Comparisons of logical operators [=, <>, ><, <=, >=].

Note

When a formula both have a plus (+) or minus (−) operators, or both multiplication (*) or division (/), calculation always proceeds from left to right and never the other way around. In computer computations, the (*) is the sign for multiplication, slash (/) for division, and caret (˘) for the exponents.

To enter a formula in a cell or Formula bar, do the following:

  1. Move the active cell indicator where you want the result to be displayed.

  2. Do one of the following:

    • Click the Formula bar then type the = (equal sign).

    • Type = (equal sign) on the active cell to indicate the beginning of a formula.

  3. Type the cell reference of the first item to include in the formula.

  4. Press the math symbol that corresponds to the math operation you wish to perform.

  5. Type the cell reference of the second item to include in the formula.

  6. Repeat steps 4 and 5 until all the cell references you wish to include are in the formula.

  7. Do one of the following:

    • Press Enter.

    • Press Tab or an arrow to move to another cell.

    • Click Enter.

Note

If you don't want to enter your formula, press Esc or click Cancel.

Exercise 1

  1. Open a new workbook.

  2. Copy the data as shown below.

  3. You can copy the formatting of the data as per instruction of your teacher.

  4. Using the formula bar, type the formula in column E to get the cost of rentals.

  5. Save your work as Formula1.xls on the Excel-Activities folder.

Enter a Range within a Formula by Dragging

Aside from typing the cell address needed in creating formulas, you can make use of the mouse pointer to select cells while typing.

To create a formula using the mouse pointer, follow these steps:

  1. Select the cell where you want the result of the formula to appear or be displayed.

  2. Type = (equal sign) to begin with the formula.

  3. Click a cell or type a number.

  4. Type an operator (+, −, *, /, ⁁).

  5. Click another cell or type a number.

  6. Repeat steps 3-5 if needed.

  7. Do one of the following to complete the formula:

    • Press Enter.

    • Press Tab or an arrow key.

    • Click Enter.

Aside from that, you can also make use of the functions that are built-in on your computer.

Note

Formulas can also be copied using Auto fill. If cells are adjacent to one another and have the same formula, you can make use of the fill handle to copy the formula instead of typing it in every cell.

Exercise 2

  1. Open the file Formula1.xls.

  2. Delete all data under the Cost column.

  3. Enter a formula on cell E7 then perform dragging to copy the formula to the cells below.

  4. Click Save and close MS Excel.

Edit Formulas

To edit formulas, do the following:

  1. Select the cell that contains the formula to be edited.

  2. Do one of the following:

    • Click the Formula bar with the I-beam mouse pointer to place the insertion point.

    • Double-click the cell to place the insertion point within the cell.

    • Press F2.

  3. Make the necessary changes you want in the formula.

  4. Click Enter when finished.

Whiz Words

Formula

Range

Operators

Marquee

Lesson Summary

Formulas allow you to create calculations for your data. In MS Excel, formulas always begin with an equal (=) sign followed by a cell address, operators, and numbers needed. Operators include the four basic operations and other mathematical operators.

There are different ways of editing formulas. You can double-click, press F2, or click the Formula bar to place the Insertion point on the cell that contains the formula you want to edit.

Study Help

Directions: Answer the following.

  1. What is a marquee?

  2. Define formula.

  3. How do you edit a formula?

Activity 1

  1. Open a new workbook.

  2. Copy the data as shown below.

  3. Type a formula to compute the ff.

    • Total Expenses for Transportation

    • Total Expenses for Lunch

    • Total Expenses for Others

    • Amount Consumed

      • Daily

      • Weekly

      • Monthly

      • In a Year

  4. Save your work as My Budget.xls on Excel-Activities.

Related

Категории