PRACTICAL-01&02 Essentials of Computer AND Spredsheet Modelling Manvi Sogani 2020BCMH006
PRACTICAL-01&02 Essentials of Computer AND Spredsheet Modelling Manvi Sogani 2020BCMH006
PRACTICAL-01&02 Essentials of Computer AND Spredsheet Modelling Manvi Sogani 2020BCMH006
ESSENTIALS OF
COMPUTER
AND
SPREDSHEET MODELLING
MANVI SOGANI
2020BCMH006
PRACTICAL-01
TIME-40minutes
PURPOSE
After completing this practical, I am
aiming to find out the differences in fees
depending upon the institute and subject.
STEPS PERFORMED IN THIS
ACTIVITY
The case study was read and understood
thoroughly.
The information which was given in the
question was noted.
Then the values were calculated for fees which
were varied by the differences of institute and
subjects.
Through those values total fees was calculated.
RESOURCE MATERIAL
USED
Case Study
Laptop
Microsoft Excel
Microsoft Word
SKILLS ACQUIRED
INDEX-MATCH Function
SUMPRODUCT Function
After completing this project, I am able
to use effective spreadsheet modelling as
best to my use.
CONCEPT INVOLVED
The fees were calculated using index-
match function.
EXCEL SHEETS
Tuition Fees
Institution
Name Physics Chemistry Maths Biology
Yogi Sir 4000 5000 8000 5000
Merit India 6500 6500 6500 6500
Scholar 7000 10000 8000 5000
Score Well 5000 5500 7000 7500
Cent Percent 5500 5500 70000 7000
Top Ranker 3500 3500 4000 4000
Name Of Institution
Student Name Subject Fee (Rs)
AAA Yogi Sir Maths 8000
AAA Yogi Sir Chemistry 5000
BBB Merit India Maths 6500
BBB Cent Percent Physics 5500
BBB Merit India Chemistry 6500
CCC Score Well Biology 7500
CCC Top Ranker Maths 4000
DDD Scholar Biology 5000
Total 48000
CONCLUSION
After completing this assignment, I am
able to calculate index-match function,
sum-product function.
PRACTICAL-02
TIME- 70minutes
PURPOSE
After completing this assignment, I
should be able to use advance filter in excel
efficiently.
I am aiming to find out high deposits
and high withdrawal for a certain time
period.
I also aim to find out deposits and
withdrawals between two dates.
RESOURCE MATERIAL
USED
Case Study
Laptop
Microsoft Excel
SKILLS ACQUIRED
SUMIFS Function
IF Function
CONCEPT INVOLVED
The high deposit of the data was
calculated using IF Function.
The formula is as follows-
E7= IF (B7>=$B$3,"*","")
And drag this till where the range ends i.e.
E13.
The formula is-
IF(deposit >= flag deposit, it will show *, if
not it will be blank)
The high withdrawal of the data was
calculated using IF Function.
The formula is as follows-
F7= IF (C7>=$B$4,""," ←")
And drag this till where the range ends i.e.
F13.
The formula is -
IF(deposit <= flag withdrawal, it will show
blank, if not it will show ←)
F2=SUMIFS(B7:B13,$A$7:$A$13,">="&$E$2,
$A$7:$A$13,"<="&$E$3)
The formula is
F2= SUMIFS(select the range of deposit,
select the range of dates, then using >= first
date, select the range of dates,<= select the
last date).
For calculating withdrawals, between
two dates SUMIFS Function is used.
G2=SUMIFS(C7:C13,$A$7:$A$13,">="&$E$2
,$A$7:$A$13,"<="&$E$3)
The formula is
G2= SUMIFS(select the range of
withdrawals, select the range of dates, then
using >= first date, select the range of
dates,<= select the last date).
EXCEL SHEETS
Saving Amount
Opening balance 50000
Closing Balance
Flag Deposit >= 100000 with *
Flag Withdrawal >= 20000 with ←
High High
Date Deposit Withdrawal Balance Deposit Withdrawal
01-07-2011 40000 50000
05-07-2011 15000 35000
10-07-2011 10000 25000
16-07-2011 150000 175000 *
23-07-2011 30000 145000 ←
24-07-2011 10000 135000
25-07-2011 15000 150000