»Rules of PrecedenceIf you combine several
operators in a single formula, Microsoft Excel performs the operations in the
order shown in the following table. If a formula contains operators with the
same precedence, for example, if a formula contains both a multiplication and
division operator, Microsoft Excel evaluates the operators from left to right.
To change the order of evaluation, enclose the part of the
formula to be
calculated first in parentheses.
Description

Operator

Reference operators

(colon),
(comma), (single space)

Negation (as in –1)



Percent

%

Exponentiation

^

Multiplication and division

* and /

Addition and subtraction

+ and –

Connects two
strings of text
(concatenation)

&

= Comparison

<
> <= >=
<>=

The following are examples of some commonly used
formulas in Microsoft Excel
Formula

Description

=D2+E2F2

If D2=6, E2=2, and F2=4, the result will be 6+24=4

=D2*E2

If D2=6, and E2=2, the result will be 6*2=12

=D2/E2*F2

If D2=6, E2=2, and F2=4, the result will be 6/2*4=12

=D2*E2+2

If D2=6, and E2=2, the result will be 6*2+2=14

=D2+2*E2

If D2=6, and E2=2, the result will be 6+2*2=10
The reason for this is that multiplication has priority over
addition, and so E2 is multiplied by 2 first, then the result
is added to D2. See how to override this in the following
example.

=(D2+2)*E2

If D2=6, and E2=2, the result will be (6+2)*2=16
Note that we have overridden the rules of precedence by
using the parentheses.

Formula

Description

=D2*(1+50%)

If D2=6, the result will be 6*(1+50%)=9

=D2^2

If D2=6, the result will be 62 =36

=D5&"
"&E5

If that cell D5 contains the first name (Ahmad), and cell E5
contains the last name (Samer), the result will be
“Samer Ahmad”

=D2>E2

If D2=6, and E2=2, the result will be TRUE

=D2<E2

If D2=6, and E2=2, the result will be FALSE

»Editing Formulas
1 Click
the cell that contains the formula you want to edit.
2 In the
formula bar, make the changes to the formula. If you want to edit a function in
the
formula, edit the arguments in the function:
3 Press
ENTER.
»Moving and Copying Formulas
1 Select
the cell that contains the formula you want to move or copy.
2 Point
to the border of the selection.
3 Tomove
the cell, drag the selection to the upperleft cell of the paste area.
Microsoft Excel replaces any existing data in the paste area.
4 To
copy the cell, hold down CTRL as you drag.
Note: You can also copy formulas into adjacent cells by using
the fill handle.
Select the cell that contains the formula, and then drag the
fill handle over the range you want to fill.
»Replacing Formulas with Values
When you replace a formula
with its value, Microsoft Excel permanently removes the formula. If you
accidentally replace a formula with a value and want to restore the formula, click
the Undo button immediately after you enter or paste the value.
1 Select
the cell that contains the formula.
2 Click
the Copy button.
3 On the
Edit menu, click Paste Special.
4Under Paste, click Values.
»Troubleshooting Formulas and
Error Values
If a formula cannot properly evaluate a
result, Microsoft Excel will display an error value. For example, error values
can be the result of using text where a formula expects a numeric value,
deleting a cell that is referenced by a formula, or using a cell that is not
wide enough to display the result.
Error values might not be caused by the
formula itself; a cell referenced by the formula may contain the error.
Error

Meaning & Solution

#####

The numeric value entered into a cell is
too wide to display within
the cell.
You
can increase the width of the column by dragging the boundary
between
the column headings or by changing the number format for
the
cell.

#VALUE!

The #VALUE!
error value occurs when the wrong type of argument
or operand is used.
Make
sure the formula or function is correct for the required
operand
or argument, and that the cells that are referenced by the
formula
contain valid values.

#DIV/0!

The #DIV/0! error value occurs when a
formula divides by 0 (zero).

#NAME?

The #NAME?
error value occurs when Microsoft Excel doesn't
recognize text in a formula.
Make
sure that cell & range names you used in your formula are
correct.

#N/A

The #N/A error
value occurs when a value is not
available to
a function or a formula.
If
certain cells on your worksheet will contain data that is not yet
available,
enter #N/A in those cells. Formulas that refer to those
cells
will then return #N/A instead of attempting to calculate
a
value.

#REF!

The #REF!
error value occurs when a cell reference is not valid.
This
error might be caused by deleting cells referred to by other
formulas,
or pasting moved cells over cells referred to by other
formulas.
Change the formulas, or restore the cells on the worksheet
by
clicking Undo immediately after you delete or paste the cells.

No comments:
Post a Comment