Creating Formulae
After entering the data in
worksheet, you can perform calculations on the data in the worksheet. In order
to create formulae, you first need to know the syntax that describes the format
for specifying a formula.
In Calc, you can enter formulas in
two methods, either directly into the cell or at the input line. Formula in
Calc may start with equal (=) or plus(+) or minus(–) sign followed by a
combination of values, operators and cell references. But, as a general
practice, all formulas should start with an equal sign. If any formula starts
with a + or –, the values will be considered as positive or negative
respectively.
Operators are symbols for doing
some mathematical,
Statistical and logical calculations. Combination
of values, operators and cell references is called as “Expression”. Calc supports
a variety of operators which are categorized as:
1.
Arithmetic
Opertors
2.
Relational
Operators
3. Reference Operators
4. Text Operator
Arithmetic operators are symbols
for performing simple arithmetic operations such as addition, subtraction,
multiplication, division etc., These operators return a numerical result.
Formula bar shows the formula what
the user had entered. But, the cell shows the resulted value (Figure 7.15).
Relational operators are symbols
used for comparing two values such as greater than, less than, equal to etc.
The relational operators are also called as "Comparative operators". These operators return either a
True or a False.
Reference operators are used to
refer cell ranges. A continuous group
of cells is called as “Range”. There
are three types of reference
operators that are used to refer cells in calc; they are (1) Range Reference
Operator, (2) Range Concatenation (3) Intersection Operator.
Colon (:) is the range reference
operator. It is used to group a range of cells. An expression using a range
operator has the following syntax:
reference
left : reference right
where reference left is the
starting cell address of a linear group of cells or upper left corner address
of a rectangular group
Reference right is the last cell
address of a linear group or lower right corner address of a rectangular group
of cell.
Example:
(i) Linear group of
cells A1, A2,A3,A4,A5 is referred as A1:A5
(ii) Rectangular group
of cells A2, A3, A4, ….. B2, B3, B4,….D5, D6 is referred as A2:D6 (Refer Figure
7.17)
Name box shows the
reference A2:D6 corresponding to the cells included in the drag operation with
the mouse to highlight the range.
Concatenation means
joining together. Tilde (~) symbol is used as a concatenation operator in calc.
An expression using a concatenation operator has the following syntax:
reference
left ~ reference right
If you want to find
the sum of the values from A1 to A6 and C3 to F3. The formula is =SUM(A1:A6
~ C3:F3)
SUM is a function to find the sum of a group of values.
Intersection operator
is used to join two set of groups. It is very similar to Range concatenation
operator. The intersection operator is represented by an exclamation
reference left !
reference right
Example:
(A2:D3 ! B2:E4)
The result of (A2:D3 !
B2:E4) is referred by the range B2:D3, because these cells are both inside
A2:D3 and B2:E4 (Refer Figure 7.19 and 7.20).
In Calc, “&” is a text operator
which is used to combine two or more text. Joining two different texts is also
known as “Text Concatenation” (Refer Figure 7.23). An expression using the text
operator has the following syntax:
text reference1 & text reference2
When arithmetic operators are used
in a formula, Calc calculates the results using the rule of precedence followed
in Mathematics. The order is:
I. Exponentiation ( ^ )
II. Negation ( - )
III. Multiplication and Division ( *, /)
IV. Addition and Subtraction (+, -)
Here is an example to illustrate
how to create a formula:
Create a Marks worksheet with the
following data:
After completing the data entry, your worksheet will look as
shown in Figure 7.22.
To
construct a formula, follow the steps below:
• Formula should begin with an = sign.
• In a formula, use only cell reference (cell addresses) instead of the actual values within the cells.
• While constructing a formula, BODMAS rule should be kept in mind.
• General Syntax of constructing a formula is: = cell reference1 <operator> cell reference2 <operator> ……………….
• Cell references are of two types (i) Relative cell reference (ii) Absolute Cell reference.
• If you refer cell addresses directly while constructing formulae, it is called as “Relative Cell reference”.
Examples of Relative Cell references:
• In the above table, all cell references are “Relative cell references”.
• While writing a formula, if you use the $ symbol in front of a column name and row number, it will become an “Absolute Cell reference”.
• Examples of Absolute cell references:
Adding values of A1, B1, C1, D1 =$A$1+$B$1+$C$1+$D$1
Subtract E4 from H3 = $H$3 – $E$4
Multiply A5 and B5 = $A$5 * B5
Average of G1, G2, G3, G4 =($G$1+G2+$G$3+G4)/4
In an expression, all
cells need not necessarily be relative or absolute. You can mix both type of
references.
The following section
explains the use of relative cell reference. About “Absolute cell reference”,
you will be learn later in this chapter.
·
Move the cell pointer to H2 (Total column)
·
Type the following formula; after entering the formula, press
“Enter” key = C2+D2+E2+F2+G2 (Refere Figure)
·
Now, you will get the sum of all the values of C2, D2, E2, F2
and G2
·
The above-mentioned formula clearly stated that, how worksheets
are working with cells.
·
While referring to the cell addresses in a formula, the
spreadsheet reads the value inside the cell that you refer. This is a good
practice of constructing a formula. Because, if you change any value, the
spreadsheet recalculates with that new value.
After entering a
formula the result is display as in Figure 7.23
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2024 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.