آموزش Excel 2000-EN

[A M I R]

[h=2]Complex Formulas

[h=3]Complex Formulas
Simple formulas have one mathematical operation. Complex formulas involve more than one mathematical operation.

The order of mathematical operations is very important. If you enter a formula that

contains several operations, like adding, subtracting and dividing, Excel knows to

work those operations in a specific order. The order of operations is


Operations enclosed in parenthesis

Exponential calculations (to the power of)

Multiplication and division, whichever comes first

Addition and subtraction, whichever comes first

Relative to the order, you will also calculate from left to right. Let's look at an example.

2*(6-4) =?

Is the answer 8 or 4? If you calculated in the order in which the numbers

appear, ignoring the parentheses, 2*6-4, you'd get the wrong

answer, 8. You must follow the order of operations to get the correct answer



​

 

[A M I R]

[h=2]Complex Formulas

To Calculate the Correct Answer,

Calculate the operation in parenthesis 6-4=2.
Multiply 2*2=4.
The answer is 4.

When using formulas, the results change each time the numbers are edited.

Remember: In Excel, never do math "in your head" and type the

answer in a cell where you would expect to have a formula calculate the answer.

​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Creating Complex Formulas
When you create a complex formula, Excel automatically follows the order of

operations. So, if you want a certain portion of the formula to be calculated

first, put it in parentheses. For example, =(B8+B9)*A10


Enter the numbers you want to calculate.

Click the cell where you want the formula result to appear.

Type =.

Click on the first cell to be included in the formula. The reference displays where you want your result.

Type a mathematical operator (Ex: the addition symbol +). The operator displays in the cell and Formula bar.

Click on the second cell in the formula. The reference displays where you want your result.

Type the next operator in the formula. The operator displays in the cell and Formula bar.

Click on the third cell to be included in the formula. The reference displays where you want your result.

Repeat steps 7 and 8 until the formula is complete, adding parentheses where necessary.

Very Important: Press Enter or click the Enter button

on the Formula bar. This step ends the formula.

Try changing one of the values in the formula and watch the answer to the formula change.



​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Filling Formulas

Once you've created a working formula, don't waste time typing it over and over

again. Simply fill it to the other cells. In order to do this, you will need to recognize

the mouse pointer's various shapes. When you move the mouse pointer around

the Excel window, it changes shape and function

If you're going to copy a formula to a surrounding cell, you can use

the Fill method. The square box in the lower right corner of the cell pointer is the fill handle

.

​

 

[A M I R]

[h=2]Complex Formulas

To Use the Fill Handle:


Click on the cell that contains the formula.

Position the mouse pointer on the lower right corner of the beginning of the

cell pointer (fill handle). The mouse pointer becomes a black crosshair

Click and hold the left mouse button and drag to select the next cells to be filled in. The cells are now selected.

Release the mouse button. The formula is copied.

In Excel, the references change as the formula is copied. Remember, you use references to identify cells

containing the numbers you want to calculate in a formula

You can also use copy and paste to copy a formula to other cells. Click next to learn more about the copy and paste method.

​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Copying and Pasting Formulas
While you can use both the fill method and the copy and paste method to copy a

formula to surrounding cells, if you want to copy a formula to cells beyond the cells adjacent to the formula, use copy and paste




​

 

[A M I R]

[h=2]Complex Formulas

[h=5]To Use Copy and Paste:
Select the cell with the formula you want to copy

Click the Copy button. Marching "ants" appear around the copied cells.

Click the area where you want to place the duplicated formula.

Press the Enter key. The formula is copied to the new location.

Cut, Copy, and Paste are discussed in detail in the Office 2000 lessons.



​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Revising Formulas
If you entered an incorrect formula or added new data and need to change

the formula to reflect the new entries, you can revise your formula using the Range Finder or your keyboard.

​

 

[A M I R]

[h=2]Complex Formulas

What is the Range Finder?

Double-click the cell that contains the formula you want to edit.

Excel colors each cell address or range address in the formula a different color. (Below, see B4.)

Corresponding colored borders appear around the cell or range. Each colored border

is called a Range Finder. (Below, see B2 and B3.) This makes it easy to see whether a formula refers to the correct cells


[h=3]

​

 

[A M I R]

Complex Formulas

To Revise a Formula using the Range Finder:

Select the cell or range you want to revise, and drag its Range Finder

(the mouse pointer should be an arrow) to the cell or

range with which you want to replace it. (Below, see Figure 1.)
Press the Enter key or click the Enter button to complete the formula.

The new result now displays in the cell that contains the formula. (Below, see Figure 2.)

[h=3]

​

 

[A M I R]

[h=2]Complex Formulas

To Revise using the Keyboard:

Double-click the cell that contains the formula you want to revise. The Range Finder displays.

Select the cell or range you want to revise.

Select another cell or range with which you want to replace it


Press the Enter key or click the Enter button. The result now displays in the cell that contains the formula.

You can also select the cell that contains the formula, select the part of the formula you want to

revise, and type a new cell address. This is a somewhat inaccurate method, as it's easy to mistype.
​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Absolute References
Normally, when you copy a formula that uses references, Excel automatically adjusts the

reference in the pasted formula to refer to different cells relative to the position of the formula

Sometimes, when you copy a formula, you don't want one of the cell references to

change. Creating an Absolute reference, a reference to a cell that does not change

when copying a formula, solves this problem. An absolute reference always refers

to the same cell or range. It is designated in the formula by the dollar sign($).


For this exercise, delete the original formulas and start again.
​

 

[A M I R]

[h=2]Complex Formulas

To Delete a Formula:


Select the cells in which the original formulas exist. The cells are now selected.


Press the Delete key. The information in these cells is deleted.

​

 

[A M I R]

[h=2]Complex Formulas

To Create an Absolute Reference:

Enter the numbers you want to calculate. Then, create a simple formula (=A1+A2).

Click on the cell in which you want to place the formula answer.

To create an absolute reference, add a dollar sign ($) in front of the column reference and the row reference (=$A$1+A2).

Press the Enter key or click the Enter button. The answer displays in the cell.

Copy and Paste the formula to another adjacent cell. The formula now includes an absolute reference (=$A$1+C2).

​

 

[A M I R]

[h=2]Complex Formulas

[h=3]Did You Know?
Knowing the Order of Operations is absolutely essential to getting the correct answer. Spend some time memorizing it:


1. Operations enclosed in parenthesis

2. Exponential calculations (to the power of)

3. Multiplication and division, whichever comes first

4. Addition and subtraction, whichever comes first

​

 

[A M I R]

[h=2]Formatting Numbers

[h=3]Introduction
[h=4]By the end of this lesson, learners should be able to:

Format numbers


​

 

[A M I R]

[h=2]Formatting Numbers

[h=3]Formatting Numbers
By default, values are formatted as general numbers. Meaning, Excel

doesn't display zeroes that don't affect the actual numeric value of the

number. For example, Excel will display 250 but not $ 250.00 unless you format the number

Values can be formatted as currency, percentages, fractions, dates, and

many other formats. Remember, when you format a number, you're not changing the numeric value


You can format selected cells using the Formatting toolbar, the Format dialog box, or the shortcut menu.

​

 

[A M I R]

[h=2]Formatting Numbers

To Format Cells Using the Formatting toolbar:

Select the cell or range you want to format.

On the Formatting toolbar, click one of the buttons explained in the table below. Your number is now formatted.

Click anywhere outside the cell to turn the formatting off.

	Currency	Displays and aligns dollar signs, comma separators, and decimal points. Ex: 89.5 as $89.50
	Comma	Same as currency, but without dollar signs. Ex: 120.5 as 120,50
	Percentage	Displays numbers as percentages. Ex: 0.82 as 82%
	Increase Decimal	Ex: .12 as .120
	Decrease Decimal	Ex: 0.5670 as 0.567

[FONT=Arial, Helvetica, sans-serif]Remember, formatting only affects the look of a cell, not the cell's contents. To

view the contents of a cell, click on the cell and look at the formula bar

[FONT=Arial, Helvetica, sans-serif]Excel has several number formats to choose from in the Format Cells dialog box. Click next to learn more.
​

 

[A M I R]

[h=2]Formatting Numbers

[h=3]To Format using the Format Cells Dialog Box:

• Select the cells you want to format

Choose Format

Cells from the menu bar. The Format Cells dialog box

opens. (Alternately, right-click on the cells to open the shortcut menu and choose Format Cells.)


The Format Cells dialog box has several categories to choose from, each one

on a separate tab: Number, Alignment, Font, Border, Patterns, and Protection


Click the Number tab if it is not already displayed.

Click on the different categories to preview results.


Set other options if you like (font, borders, etc).

Click OK.

If you hover your mouse pointer over the dollar sign button on the

Formatting toolbar, the Tooltip reads "currency style." When you

apply this button's format, Excel applies the accountant format(dashes for zeroes and dollar signs lined up, right next to the numbers).
​

 

[A M I R]

[h=2]Formatting Numbers

[h=3]Challenge!
Open Excel.

Experiment and type a few numbers into cells. Select the cells.

Using the Formatting toolbar, convert cells to currency, comma, and percentage.

Open the Format Cells dialog box. Click the Numbers tab.

Preview the various categories.

Click OK.

​