Home

PRACTICAL QUESTIONS AND ANSWERS

PRACTICAL NO:1

SPREAD SHEET FUNCTIONS- COUNT, COUNTA, COUNTIF

QUESTION: The following table is given to you


Find out:

a). How many cell contains numbers only.

b). Count the number of cells containing any value

c). Count the number of cells containing the value exceeding 1000

PROCEDURE:

  • i).Open a blank spread sheet in LibreOffice Calc
    • Applications → Office → Libre Office Calc
  • ii).Enter the given data in A1 to J2
    • (In the same sequence as given in the table, including the blank spaces)
  • iii).For getting the number of cells containing numbers, give the formula in B3
    • =COUNT(A1:J2)
  • iv).For getting the no of cells that contains any value, give the formula in B4
    • =COUNTA(A1:J2)
  • v).For getting the no. of cells which has values exceeding 1000, given the formula in B5
    • =COUNTIF(A1:J2,”>1000”)

PRACTICAL NO:2

SPREAD SHEET FUNCTIONS MAX (), MIN (), AVERAGE(), CONCATENATE ( )

QUESTION: The following is the scores obtained by some students in a
competitive examination. Find out the ‘Highest’ , ‘Lowest’ and ‘Average’
score using appropriate functions in spread sheet.


PROCEDURE:
  1. Open a new Spread sheet in LibreOffice Calc.
    1. Applications → Office → Libre Office Calc
  2. Enter all given values in spread sheet as given in the question.
  3. Find the highest Score in cell B3 by =MAX(B2:H2)
  4. Find the lowest rank in cell B4 by =MIN(B2:H2)
  5. Find the Average mark in cell B5 = AVERAGE(B2:H2)

PRACTICAL NO: 3

SPREAD SHEET FUNCTION – IF
Question:
The XYZ Company Ltd. Furnishes you the list of their employees and
their taxable income

Compute tax based on the following criteria by using appropriate spread
sheet function.
i) If the taxable income is below 250000 tax is Nil
ii) If the taxable income is 250000 to 500000 tax rate is 10%
iii) If income is above Rs. 500000 tax is @ 20%


PROCEDURE:
i). Open a new spread sheet in LibreOffice Calc.
ii). Enter all labels in the spread sheet as A1-Name, B1-Sex, C1-Taxable
Income and D1-Tax, then enter all table values in the corresponding
cells.
iii)select the cell D2 and type the formula as
=IF(C2>=500000,C2*20/100,IF(C2>=250000,C2*10/100,"NIL"))
Then drag and copy formula to the following cells



PRACTICAL NO:5
SPREAD SHEET - FUNCTION – NESTED IF
QUESTION: Richu obtained the following Scores (out of 100) in his HSE
Examination March 2016.
Subjects Scores
ENGLISH 85
MALAYALAM 96
BUSINESS STUDIES 75
ACCOUNTANCY 67
ECONOMICS 29
COMPUTER
APPLICATION 45
Convert the above scores into Grades for each subject using the following criteria
Grade Criteria:
Score Grade
90-100 A+
80-89 A
70-79 B+
60-69 B
50-59 C+
40-49 C
30-39 D+
20-29 D
LESS THEN 20 E
--------------------------------------------------------------------------------------------

Procedure :
i) Open blank spread sheet in LibreOffice calc.
Applications → Office → Libre Office Calc
ii). Enter all Labels in the spread sheet A1-Subject, B1-Score, C1- Grade and
enter all the details as given in the questions
iii).In the cell C2 Enter

=IF(B2>=90,”A+”,IF(B2>=80,”A”,IF(B2>=70,”B+”,IF(B2>=60,”B”,IF
(B2>=50,”C+”,IF(B2>=40,”C”,IF(B2>=30,”D+”,IF(B2>=20,”D”,”E”)
)))))))
Then drag and copy the formula to the following cells



PRACTICAL NO:6
SPREAD SHEET -FUNCTION – SUM (), SUMIF (), LOOKUP ()
QUESTION: Given below is a table showing the name, Designation and
Monthly Salary paid for different employees in Rajan Traders for March 2016
Employee Name Designation Monthly Salary (Rs)
JOSE CEO 80000
CICY AO 50000
SIJO FM 40000
ALEX FM 15000
JOMY MM 45000
SIMI FM 30000
Find out the following
(a). The Total Monthly salary using naming function , give the name as
” TOTAL _SALARY”.
( b). The Total Monthly Salary paid to the Finance Managers(FM) in the firm
(c ). The Name of the employee with Monthly Salary of Rs. 40000 by using
“LOOKUP” Function.
--------------------------------------------------------------------------------------------------------
Procedure:
i).Open New spread sheet in LibreOffice calc.
ii).Enter all column headings in the spread sheet. A1-Name of the Employee,
B1- Designation, C1-Monthly salary and enter the all details given in the
questions.
iii). Select the range of cells for which the name is to be assigned.(C2:C7)
iv). Go to 'Data' Tab, click “Define Name” option.
v). In the dialogue box, type “TOTAL_SALARY” against the Name box.
Then click Ok button.
vi).Calculate total monthly salary in cell C8 by
=SUM(TOTAL_SALARY)
vii).Calculate the monthly salary paid to FM in the cell C9 by
=SUMIF(B2:B7,”FM”,TOTAL_SALARY)
viii). Find the name of employee with monthly salary Rs.40000 in the cell C10
by =LOOKUP(40000,TOTAL_SALARY,A2:A7)

Post a Comment