»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)
|
No comments:
Post a Comment