Formulas Entering and Calculatione Operators Excel

»What is a Formula?A formula is an equation that analyzes data on a worksheet. Formulas perform operationssuch as addition, multiplication ,and comparison on worksheet values; they can also combine values. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks.

»Formula Syntax
Formula syntax is the structure or order of the elements in a formula. Formulas in Microsoft
Excel follow a specific syntax that includes an equal sign (=) followed by the elements to be calculated (the operands) and the calculation operators. Each operand can be a value that does not change (a constant value), a cell or range reference, a label, a name, or a worksheet function.

By default, Microsoft Excel calculates a formula from left to right, starting with the equal sign (=). You can control how calculation is performed by changing the syntax of the formula (will be covered in subsequent sections). The following illustration shows a simple formula that multiplies the Quantity by the Unit Price to arrive at the Total Price.

Notice that the formula started by an equal sign (=). Cells D4 and E4 are the operands. The multiplication sign (*) is the operator.

»Entering Formulas
1- Click the cell in which you want to enter the formula.
2- Type = (     an equal sign). Note that if you click Insert Function button, Microsoft Excel
inserts an equal sign for you:

3-Enter the formula.
4- Press ENTER.

Note: You can enter the same formula into a range of cells by selecting the range
first, typing the formula, and then pressing CTRL + ENTER.

»Calculation Operators
Operators specify the type of calculation that you want to perform on the elements of 
a formula. Microsoft Excel includes four different types of calculation operators: arithmetic,
comparison, text, and reference.

1-Arithmetic operators perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results.

Arithmetic operator 
3+3, D4+E4
+ (plus sign)
3-1, E9-5
– (minus sign)

3*3, D4*E4
(asterisk)    *
3/3, C8/B8
(forward Slash )/
% (percent sign)
3^2 (the same as 3*3)
^ (caret)  

2-Comparison operators compare two values and then produce the logical value TRUE or FALSE.

Comparison operator                                                    Meaning  Example
=  (equal sign)                                                                 Equal to   A1=B1
>  (greater than sign)                                                     Greater than A1>B1
<  (less than sign)                                                           Less than  A1<B1
>= (greater than or equal to sign)                                     A1>=B1
<= (less than or equal to sign)                                            A1<=B1
<> (not equal to sign)                                                     Not equal toA1<>B1

3-The text operator "&" combines one or more text values to produce a single piece of text. For example: “North” & “wind” produce “Northwind”

4-Reference operators combine ranges of cells for calculations (You are advised to study functions before going into these operators).

Ref. operator
: (colon)
Range operator, which  produces
one reference to all  the cells
between two references,  including
the two references
, (comma)
Union operator, which  combines
multiple references into one
(single space)
Intersection  operator,  which
produces one reference to cells
common to two references. In this
example, cell B7 is common to both
SUM(B5:B15 A7:D7)

No comments:

Post a Comment