Chapter -4 (Formulas and Functions) Ques/Ans)
D. Answer the following questions –
1. What is a formula? Explain Basic and Compound Formula with example.
Ans Formula is an expression that can include cell addresses, numbers, arithmetic operators,
and parenthesis. It is used to perform calculations involving addition, subtraction,
division and multiplication. A formula must begin with an equal to (=) symbol.
Basic Formula – Basic Formula involves only one type of operator in it. For example, we
can calculate the sum of numbers in B2,B3,….,B7 by using formula
=B2+B3+B4+B5+B6+B7.
Compound Formula - Compound Formulas are used when more than one operator is
required to perform calculation. For example, we can calculate the Simple Interest using
the formula =P*R*T/100
P- Principal Amount, R- Rate of interest, T- Time
2. What is cell reference? Mention its types.
Ans. The cell address in a formula is known as the cell reference. For example, A1 where A
denotes the Column name and 1 denotes the Row number. References help in finding
the values or data in a worksheet, that we want to use in formula. There are three types
of cell references: Relative reference, Absolute reference, and Mixed reference.
3. Describe Absolute Referencing.
Ans. Absolute reference is used when we do not want to change the address of the cell
while copying the formula to the other cell. To use absolute reference in a formula,
dollar sign ($) is used before the column and row number. Example: =$A$1+$A$
4. Differentiate between the Concat() function and TextJoin() function.
Ans The Concat() function is used to join two or more than two strings together. Whereas,
Textjoin() lets us join the text specified in a range of cells where we can add words and a
delimiter we specify between each text value that will be combined.
Extra Questions :
1. What do you mean by Function? What are the rules to enter a function?
Ans. Functions are the pre-designed formulas in Excel to perform both simple and complex
calculations. They accept Arguments and return Values.
Rules to enter a Function –
1. All Excel function must begin with ‘=’ sign.
2. Function name must be a valid Excel name. For example, SUM, AVERAGE.
3. Function name must be followed by an opening and closing parenthesis.
4. Arguments are enclosed in the parenthesis. For example, =SUM(A1:A5)
5. Function names are not case-sensitive.
2. Write the names of group and tab where these options or buttons are available.
1) Cut, Copy & Paste button → Clipboard group on the Home tab
2) Insert drop-down button → Cells group on the Home tab
3) Delete drop-down button → Cells group on the Home tab
4) Format drop-down button → Cells group on the Home tab
5) AutoSum button → Editing group on the Home tab
6) Merge & Center option → Alignment group on the Home tab
7) Background option → Page Setup group on the Page Layout tab
3. Explain the following functions with example.
1) SUM() 2) AVERAGE() 3) MAX() 4) MIN()
5) SQRT() 6) POWER() 7) MOD()
1) SUM() – The Sum function returns the total of the range values.
=SUM(range)
For example =SUM(B6:D6) It gives the total of the range values from cell B6 to cell D6.
2) AVERAGE() – The Average function finds the average of the range values.
=Average(range)
For example =AVERAGE(B6:D6) It gives the average of the range values from cell B6 to
cell D6.
3) MAX() – It is used to find the largest value in the given range.
=MAX(range)
For example =MAX(B6:D6) It returns the maximum value in the range B6:D6.
4) MIN() – It is used to find the lowest value in the given range.
=MIN(range)
For example =MIN(B6:D6) It returns the minimum value in the range B6:D6.
5) SQRT() – It is used to find the square root of given number.
=SQRT(number)
For example =SQRT(49) It returns the value 7 which is square root of 49.
6) POWER() – It returns the result of a number raised to some power.
=POWER(number,power)
For example =POWER(2,3) It gives the value of 23 i.e. 8.
7) MOD() – It returns the remainder after a number is divided by divisor.
=MOD(number,divisor)
For example =MOD(18,5) It gives the remainder 3 after the division 18 by 5.