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.

Examples	Meaning	Arithmetic operator
3+3, D4+E4	Addition	+ (plus sign)
3-1, E9-5	Subtraction	– (minus sign)
–1	Negation
3*3, D4*E4	Multiplication	(asterisk)    *
3/3, C8/B8	Division	(forward Slash )/
20%	Percent	% (percent sign)
3^2 (the same as 3*3)	Exponentiation	^ (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	Meaning	Example
: (colon)	Range operator, which  produces one reference to all  the cells between two references,  including the two references	B5:B15
, (comma)	Union operator, which  combines multiple references into one reference	SUM(B5:B15,D5:D15)
(single space)	Intersection  operator,  which produces one reference to cells common to two references. In this example, cell B7 is common to both ranges	SUM(B5:B15 A7:D7)