Class-7
Chapter 1
Formulas in excel 2013
1. A formula in Excel is a sequence of values, cell
references, functions and or operators in a cell that
produces a new value from existing values.
2. Give Examples for each ( write three example)
(do it as Home Work)
a) Values :_________________________________
b) cell references :_____________________________
c) functions :_________________________________
d) operators :_________________________________
3.A reference identifies a cell or range of a cells in a
worksheet, and tell excel where to look for values or data we
wanted to use in a formula.
Eg:
a) B1
b) C2:C10
c) 6:6
d) G:G
e) C:E
4. To enter a formula select a cell and enter the formula in the
cell or on the Formula Bar.
5. We can see the formula in the Formula Bar when the cell is
selected.
6. The easier method of adding cell reference to a formula is to
click on the cell instead of typing the cell address. This
method is called pointing.
Date : 2-9-2020
[Link] two ways of entering cell reference in a formula are
a) By typing
b) By pointing
[Link] between pointing method and typing method:
Adding a cell reference to a formula by clicking on the
cell is called Pointing.
Adding a cell reference to a formula by entering in the
cell or formula bar is called typing.
[Link] we use a cell (or range ) reference in a formula we
can use any of the following references.
a) Relative reference
b) Absolute reference
c) Mixed reference
10. In relative reference the row and column reference
can change when we copy the formula to another cell.
11. By default Excel creates relative cell references in a
formula. For eg : A5, D4, C3 etc.
12. Absolute reference : The row and column reference do not
change when you copy a formula because the reference is to a
fixed cell address.
13. Absolute reference uses two dollar sign in the reference,
one for the column letter and other for the row number. Eg.
$A$3, $G$7
14. Mixed reference : The row or column reference is relative
while the other is absolute. Eg: $A1, B$1
15. To view the formula copied in to the cell range follow the
given steps
Click the FORMULAS tab
In the Formula auditing group click the Show formula
button.
(The formula used in each of the cells of the
range will be shown in cells)
16. Circular Reference: A circular reference occurs when a
formula refers to its own value.
Eg: If you enter a formula =A1+A2+A3 in cell A3 every time
it calculates the formula in cell A3, and calculated again and
again because A3 has changed. (This calculation would go on
forever. You will see a warning in such a case)
[Link] formula : A text string or a text value is a sequence
of characters on which we cannot perform operations like
subtraction, multiplication or division.
18. Concatenation : We can join string together. This is called
concatenation. We are using Ampersand (&) character to
concatenate strings. Eg: =”Good “&” “ &”Morning”
19. Mathematical operators used in formula:
Give table shows the mathematical operators used in Excel
and their order of evaluation.
Points to be noted:
Any operations contained in the bracket should be carried
out first allowed by any exponent.
Follow division and multiplication operations. It has equal
importance. They are carried out in the order in which they
occur in the formula.
After that addition and subtractions operations are given
equal importance. They also carried out in the order in
which they occur in the formula
Note: IF two operators have the same
order of evaluation Excel simply
evaluates them from Left to Right
20. Name the arithmetic operators we can use ion Excel formula.
(Write according to the order of evaluation)
Operation Operator Order of Evaluation
21. Error results:
When the formula has a problem and Excel cannot process it
the cell with a formula displays an error result rather than
showing a proper value. (Draw this table in the notebook and
learn)
22. Auto sum feature:
The Autosum features automatically adds numbers in the
selected cells. We can also use it to find average, maximum etc.
for a rage of cells.
23. Autosum button is present in two places in the ribbon
In the Editing group on the Home tab
In the Function Library group on the Formulas tab.
24. Name the options under Autosum button
____________________________________________________
____________________________________________________
____________________________________________________
____________________________________________
25. Count ( ) - The Count function counts the number of
numeric values in a range.
26. What are functions?
____________________________________________________
____________________________________________________
______________________________________________
27. What is the use of GCD( ) function in Excel
____________________________________________________
____________________________________________________
______________________________________________
28. What is use of LCM ( ) Function in Excel
____________________________________________________
____________________________________________________
______________________________________________
30. In the Formulas Tab in Function Library group we can
see different category of functions.
Do the Given Home work
Note:
If you have old text book do it in the text book.
If you don’t have text book copy the questions in note book
and answer.
Fill in the blanks answers are given. Check your answer and
correct .
a) Pointing
b) Mixed
c) Ampersand
d) ^
e) Editing
f) Formula
g) Average
h) Same
