Ex nNo:
Date: CONSOLIDATED DATA
Step 1:☑Enter the deposit and withdrawal of customers for each month in
different sheets.
AIM: Punjab National Bank has its deposit and withdrawal detail of customer’s
for 3 months. Help them to prepare a CONSOLIDATED DATAfor the above
year using Open Office Calc.
Step 2: ☑Select Sheet in which you need the consolidated report.
Step 3:☑Select Data → Consolidate
Step 4:☑Add consolidation ranges by selecting sheets. Click OK
CONSOLIDATING DATA
RESULT: Thus a Spreadsheet has been consolidated data using Open Office
Calc.
Ex.no.
Date: Applying Subtotal in OO Calc
AIM: RPS Distributers distributes products in different areas. Calculate the area
wise distribution of products using SUBTOTALS in Open Office Calc.
☑Enter the data and select the entered data
PROCEDURE:
Step 1:Select Data → Subtotal
Step 2:Select 1st Group tab. From the Group by box select Place and click the
check boxes in the calculate subtotal box.
Step 3:Click ok
Step 4: District wise Total products distributed and Grand Total will be displayed.
RESULT:Thus a Spreadsheet area wise distribution of products using subtotalsin
Open office Calc.
Ex.no.
Date: GOAL SEEK
AIM:A student is planning her goals about the marks she should attain in the
forthcoming Semester 4 examinations in order to achieve a distinction (75%).
Assuming that examination of each subject is for 100 marks, her marks of the
previous semesters are given as under. (Use GOAL SEEK in Open Office Calc)
Find out how many marks should she obtain in 4th semester to secure distinction.
Enter the data into Open Office Calc as given and leave rows for Semester4 marks
and percentage.
Step 1:Select Percentage column of Subject1 and apply the formula to calculate
percentatage. [(S1+S2+S3+S4) / 4]
Step 2:Select Tools →Goal Seek
Step 3:Enter formula cell address, Target value, and variable cell address as
semester4 cell address.
Step 4:Click OK.
Step 5:Apply the same for other subjects.
RESULT:Thus a Spreadsheet 4th semester Marks has been calculated using GOAL
SEEK in Open Office Calc.
Ex.no.
Date: SOLVER
AIM:Using SOLVER option in OpenOffice Calc, project the simple interest
amount by changing principal amount and rate of interest while calculating simple
interest.
PROCEDURE:
Enter Principal Amount, Rate of interest and year. Calculate Simple Interest using
the formula SI = (P*N*R)/100.
Step 1: Select Tools →Solver
Step 2:Fix the Interest to 1500 by changing values of Principal and Rate.
Step 3: Specify the corresponding cell addresses and apply limiting
conditions
Step 4: Click Solve.
Projected interest after changing the principal and rate of interest.
RESULT:Thus a Spreadsheet has been calculated simple interest amount by
changing principal amount and rate of interest.
Ex.no.
RESULT: Thus a Spreadsheet has been calculated by Simple interest using
SOLVER in Open Office Calc.
Date: MACRO
AIM:Create a MACRO to prepare a mark sheet of 10 students for 5 subjects
(Marks out of 100 for each subject):
☑Find average of each subject.
☑ Find maximum mark of each subject
☑ Find minimum mark of each subject
☑ Highlight the marks of each subject >75 and change the cell and font colour.
PROCEDURE:
Enter the names and marks.
Step 1:Select View →Macros→Use Relative Reference.
Step 2:Select View→Macros→Record Macros.
Step 3: Give the macro name and shortcut key for the macro.
Step 4:Apply calculations for average, maximum, minimum and highlight
marks>75 for one subject.
Step 5:Select View →Macros→View Macros.
Step 6:Select the corresponding macro name and click Run.
OR
Use the given shortcut key Ctrl+M
☑Continue the same for all columns.
MARK LIST 2024-2025
NAME ENGLISH TAMIL MATHS SCIENCE SOCIAL
TEENA 96 89 70 74 92
MEENA 85 59 68 68 58
RITU 73 73 74 59 74
HIBA 98 66 95 72 70
RITA 78 97 84 93 82
ROSE 59 72 63 85 70
AVERAGE 81.5 76 75.67 75.17 74.33
MAXIMUM 88 97 95 93 92
MINIMUM 59 59 63 59 58
RESULT:Thus a Spreadsheet a mark sheet of 10 students for 5 subjects (Marks
out of 100 for each subject):
