Editing Moving Replacing Troubleshooting and Copying Formulas

»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+E2-F2	If D2=6, E2=2, and F2=4, the result will be 6+2-4=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 upper-left 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.

4-Under 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.