Mark sheet

Roll No. Student’s Name Math English Hindi Science Social Study Total Percentage Division Result Position
1 Pankaj 78 78 65 95 78
2 Ranjan 96 98 98 86 69
3 Rajan 85 68 78 75 58
4 Harish 74 59 45 95 56
5 Vikrant 35 65 95 85 85
6 Shivam 62 74 68 68 95
7 Kundan 45 58 75 48 67
8 Suraj 95 96 95 98 49
9 Riya somani 68 89 68 78 35

Maximum
Minimum
Average

Function we Learn

Sum Percentage IF MAX MIN Average

1. Sum( )
It is mathematical function used to add the numeric value in a range of cells.
E.g. = SUM(starting cell address: ending cell address)

2. Percentage
It is not a mathematical function it is a mathematical formula.
E.g. = (Total obtain Mark/Full Mark)*100

3. IF( )
IF function can perform a logical test and return one value for a TRUE result and another for a FALSE result.
E.g. = IF(L5>60%,"1ST DIVISION",IF(L5<50%,"2ND DIVISION",IF(L5<30%,"FAIL"))))

4. Max( )
This function returns the largest value in the given range of cells.
E.g. = Max(C5:C10) - Highest value in the range C5 to C10

5. Min( )
This function returns the lowest value in the given range of cells.
E.g. = Min (C5:C10) - Lowest value in the range CS to C10

6. Average( )
This function calculates and returns the average (arithmetic mean) of the numeric values in the given range of
cells. It is statistical function.
E.g. = Average (C17:C20)
 Attendance sheet
DATE 1 2 3 4 5 6 7 8 9 10 11 12 13 14
WEEK SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT Present Absent Leave
S NO. Name Post H p a a a a p H P a a a a a
2 KMAL MANAGER H p a a a a p H P a a a a a
3 NIRAJ MANAGER H p P a a a p H P P a a a a
4 SAROJ HR H a P a p a p H a P a p p a
5 DHEERAJ STAFF H a a P p a p H a a P p p a
6 RAMESH STAFF H a a P p p a H a a P p p p
7 SURESH STAFF H p P P p p p H P P P p p p
8 SUDIP STAFF H a P P p a a H a P P p p a

Function we Learn
CountIF: one of the statistical functions, to count the number of cells that meet a criterion.
E.g. =Countif(give the range,”criteria”)
 Birthday Calculation
Current Age In Age In Age In Total Week Total
SL.No. Name D.O.B
Date Year Months Days Days day Hours
1 PANKAJ 17-02-2000
2 KMAL 27-10-2002
3 NIRAJ 05-05-1990
4 SAROJ 17-10-1900
5 DHEERAJ 25-08-2006
6 RAMESH 17-05-1995
7 SURESH 08-06-1970
8 SUDIP 17-02-1970

Function we Learn

Today DATEDIF TEXT

1. Today: It will calculate and give the current date.
E.g.= Today( )

2. DATEDIF: Calculates the number of days, months, or years between two dates.
E.g. = Dateif(Today,DOB,”YM” ) Hint: For Year=”Y”, For Month= “YM”, For Day= “MD”.

3. For Total Days =(Current Date – DOB)

4. TEXT: The TEXT function is used to convert a number to text in a specified number format.
E.g. = Text(DOB,”DDDD”) Hint : For Short Date=”DDD” Long Date=”DDDD”.

5. For Total Hours =(Total*24)

 Basic Function
Text Power Result Hour Minute Second Time
6 2 7 23 54
5 3 4 45 17
2 4
Time: allows you to create time with individual hour,
Power: Returns the result of a number raised to a minute, and second components.
power. E.g. =Time(Hour,Minute,Second)
E.g. =Power(Text,Power)

Text Roman Text Upper Case Lower Case

4 Ram
5 Mohan
6
Upper Case: you need to convert text to uppercase.
Roman: Converts an Arabic numeral to roman, as
E.g. =Upper(Text)
text.
Lower Case: you need to convert text to Lowercase.
E.g. =Roman(Text,
E.g. =Lower(Text)

Text Repeat Result Number Round Off

Ram 2 127.564
Mohan 3 2.87
Sita 4 Round: function rounds a number to a specified number of
digits.
Rept: Repeats text a given number of times. E.g. =Round(Number)
E.g. =Rept(Text,Rereat)
 Medical shop Management

SL.No. Name of Drugs From Month Date Year Manufacture Date Expiry Date Date of Today

1 Rifampicin capsul 6 12 2008 12-Jun-10

2 Simvastin tablet 5 25 2008 25-May-10

Vitamin B
3 capsul 9 2 2010 01-Sep-12
Complex
4 Quinine Sulphate tablet 3 15 2007 14-Mar-09
Aspirine
5 capsul 11 11 2009 11-Nov-11
Atorvastatian
6 Antioxidant drops 4 12 2010 11-Apr-12

7 Antioxidant capsul 12 4 2007 03-Dec-09

8 Aiprazolam liquide 9 2 2006 01-Sep-08

9 Antioxidant tablet 8 1 2007 31-Jul-09

10 Atenolol capsul 1 18 2010 18-Jan-12

Function we Learn
Today DATED
1. Today: It will calculate and give the current date.
E.g.= Today( )

2. Date : The DATE function creates a date using individual year, month, and day arguments.

E.g.=Date(Year,Month,day)

Additional Information:
A. Find out Manufacture Date using date Function.
B. Find out Date of Today.
 Employee Payroll Details - I
Emp_Code Date Of Joining Department Basic DA TA House(Yes/No) HRA Gross P.F P.Tax Net

EC/001 10-04-2006 SALES 25000

EC/002 10-02-2004 AUDIT 14000

EC/003 08-05-2004 MEDIA 8000

EC/004 02-02-2008 SALES 18000

EC/005 12-06-2009 ADMIN 9000

EC/006 20-08-2008 ACCT 6000

EC/007 10-05-2010 MEDIA 15000

EC/008 02-04-2010 SALES 20000

EC/009 01-02-2004 ACCT 5000

EC/010 15-06-2010 AUDIT 12000

Additional Information:
A. Calculate DA@ 40% of Basic Pay
Ans. =D2*40%.
B. Calculate TA@ 10% of Basic or Rs.1000/- Which ever is higher.
Ans. =MAX(D2*10%,"1000")
C. Provide House for Audit department only and – No for others.
Ans. =IF(C2="AUDIT",("YES"),"NO")
D. Calculate HRA@ 15% of Basic pay for those who do not get accommodation from company and for other it will
be 0.
Ans. =IF(G2="NO",D2*15%,0)
E. Calculate Gross salary
Ans. =Basic+DA+TA+HRA
F. Calculate P.F@ 12.5% of Basic Pay.
Ans. =D2*12.5%.
G. Calculate P.Tax as per Slab.
Upto Rs 5000 ------- Nil.
Rs. 5001 – 6000 ----- RS.60/-
Rs. 6001 – 10,000 --- Rs. 80/-
Rs. 10,001 – 15000 --- Rs. 100/-
Rs. 15001 – 20,000 --- Rs.150/-
Rs. 20,001 and above --- Rs.200/-
Ans.
=IF(D2<=5000,0,IF(AND(D2>5000,D2<=6000),60,IF(AND(D2>6000,D2<=10000),80,IF(AND(D2>1000
0,D2<=15000),100,IF(AND(D2>15000,D2<=20000),150,IF(D2>20000,200))))))
H. Calculate Net Salary (Gross-P.F- P.Tax)
Ans. =I2-J2-K2
 Employee Payroll Details – II

E_Code Name Post Basic DA Flat Yes/No HRA TA Gross P.F Net
101 Aman sr executive 6200
102 Suman sr executive 6500
103 Dinesh sr executive 7800
104 Mohan sr executive 5600
105 Sohan sr executive 6500
106 Rohan sr executive 6300
107 Ram manager 8500
108 Shyam manager 8900

Additional Information:
Post Slab are as given below
Basic Post
<=4000 Executive
>4000,<8000 Sr. Executive
>8000,<=12000 Manager
And >12000 Sr. Manager
Ans. =IF(D3<=4000,"executive",IF(AND(D3>4000,D3<8000),"sr executive",IF(AND(D3>8000,
D3<=12000),"manager",IF(D3>12000,"sr manager"))))
A. Calculate DA@ 60% of Basic Pay or 3000 whichever is higher.
Ans. =MAX(D3*60%,3000).
B. Flat is allotted to Manager and Sr. manager only.
Ans. =IF(OR(C3="MANAGER",C3="SR MANAGER"),"Yes","No")
C. HRA@20% on Basic and DA will be given to those employees who are not allotted with flat.
Ans. =IF(F3="No",(E3+D3)*20%,0)
D. Travelling allowance of Rs.3000/- will be given to Sr. manager and Manager whose basic+ DA exceed 15000/-
and for other Rs. 1000/-
Ans. =IF(C3="MANAGER",”3000”,IF(C3="SR MANAGER",”3000”,IF((D3+E3)>15000,”3000”,”1000”))).
E. Calculate Gross Salary.
Ans. =Basic+DA+TA+HRA
F. Calculate P.F 12% on (Basic+DA)
Ans. =(D3+E3)*12%
G. Calculate Net Salary.
Ans. =(Gross-P.F).
 Employee Payroll Details – III

CODE NAME DEP. BASIC PAY D.A H.R.A T.A GROSS SALARY

Additional Information:

A. Code No. of Employees go from E1, E2, E3 ………….. E10.

B. Basic pay of 1st Employees is Rs. 18000, next four Employees Rs. 15,000 and last three Employees Rs. 12000.
C. Mention only 3 Department namely Accounts, Sales & Marketing.
D. Provide D.A as 40% of Basic Pay.
E. Provide H.R.A as 30% of Basic Pay.
F. Provide T.A as 15% of basic Pay.
G. Calculate Gross Salary.
Using different formulas compute the following:

a. Count the Employees who are posted in Sales Department.

Ans. =COUNTIF(C3:C12,"SALES")
b. Add the Gross Salary of Accounts Department.
Ans. =SUMIFS(H3:H12,C3:C12,"ACCOUNTS")
c. Count the total number of Cell that contain data.
Ans. =COUNT(A2:H12)
d. Compute the LCM of 45, 20, 60 & 5.
Ans. =LCM(45, 20, 60, 5)

e. Round off the Number 102.36 to nearest 1.

Ans. =ROUND(G42,1)
f. Round off the Number 542.21 to nearest 10.
Ans. =ROUND(H43,2)
 Loan Statement

Principal 10000 Instilment

Period(Month) 10 1037.03
Rate 8%

Monthly
Month Principal Interest
Payment Additional Information:
1 970.37 66.67 1037.03
a. Calculate Instalment.
2 976.83 60.20 1037.03
Ans. =ABS(PMT(Rate/12, Period , Principal))
3 983.35 53.69 1037.03 b. Calculate Principal.
4 989.90 47.13 1037.03 Ans. =ABS(PPMT(Rate/12,1, Period, Principal))
5 996.50 40.53 1037.03 c. Calculate Interest.
6 1003.15 33.89 1037.03 Ans. =ABS(IPMT Rate/12,1, Period, Principal))
d. Calculate Monthly Payment.
7 1009.83 27.20 1037.03
Ans. = Principal+ Interest
8 1016.56 20.47 1037.03
9 1023.34 13.69 1037.03
10 1030.16 6.87 1037.03
 Item Level Discount

1. Create a new workbook and prepare the following data base with necessary functions.

P_CODE QTY RATE AMOUNT DISCOUNT RATE DISCOUNT AMOUNT NET AMOUNT

P001 42 20 10%

P002 45 15 5%

P003 50 10 5%

P004 100 11 10%

P005 52 21 4%

P006 84 33 3.33%
 Employee Payroll Details – IV

Q A. QB Q C. QD QE
CODE BASIC DEPT PF HOUSE(NO\YES) HRA DA GROSS
PE/001 5000 ACCT
PE/001 4500 ACCT
PE/001 5200 MKT
PE/001 4100 ACCT
PE/001 3200 STCK
PE/001 3550 ADMIN
PE/001 5600 ADMIN

Additional Information:

a. PF will be 12% of basic or 600 whichever is higher.

b. Provide House for Accounts dept Only.
c. Calculate HRA@15% for those who do not get accommodation from the company.
d. DA will be 10% of Basic.
e. Calculate the gross salary.
 GOWDOWN WISE Detail

INWARD DATE COMPANY NAME PRODUCT NAME GODOWN RATE QTY AMOUNT PAYMENT DATE
01.09.2010 LG MOBILE BURABAZAR 8500 20
02.09.2010 NOKIA MOBILE SOVABAZAR 11500 25
03.09.2010 MOTOROLA MOBILE MALIKBAZAR 5600 14
04.09.2010 SONY MOBILE MALIKBAZAR 11000 10
05.09.2010 LG MOBILE SOVABAZAR 8000 5
06.09.2010 LG HEADSET SOVABAZAR 5500 6
07.09.2010 NOKIA HEADSET BURABAZAR 9000 22
08.09.2010 MOTOROLA BATTERY BURABAZAR 12500 15
09.09.2010 NOKIA BATTERY BURABAZAR 150 15
10.09.2010 LG BATTERY MALIKBAZAR 300 30
11.09.2010 MOTOROLA BATTERY SOVABAZAR 6000 8
12.09.2010 MOTOROLA BATTERY MALIKBAZAR 2500 10
13.09.2010 SONY HEADSET SOVABAZAR 3500 5
14.09.2010 SONY BATTERY BURABAZAR 12000 15
15.09.2010 SONY MOBILE HOWRAH 250 25
16.09.2010 SONY HEADSET HOWRAH 21000 20
 QA TOTAL QTY OF ALL PRODUCT

MALIK
QB BURABAZAR SOVABAZAR HOWRAH
BAZAR
TOTAL VALUE OF NOKIA GOWDOWN WISE

QF MINIMUM VALUE OF PRODUCT

MORE QTY
QH
ITEMS

QI MAX VALUE OF PRODUCT

FLITER CTL+SHIFT+L
QJ
CREATION

Function we Learn

SumIF MIN MAX SUM

 1. SUMIF: The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based
on a true or false condition.

Additional Information:
a. If the total amount is more than Rs. 10,00,000 then payment date will be 15 days after inward date.
Ans. =IF(G3<100000,"PAID AFTER 15 days")
b. Find out the total quantity of all products.
Ans. =SUM(F3:F18)
c. Find Out the value of Nokia Mobile godown wise .
Ans. =SUMIFS(F2:F18,B2:B18,"NOKIA",D2:D18,"BURABAZAR")
d. Find Out minimum value products.
Ans. =MIN(F3:F18)
e. Highlight the product values which are more than Rs. 70,000.
Ans. =MAX(F3:F18)
f. Which godown is having more quantity of items?
Ans. --------
g. Find out maximum value product.
Ans. =MAX(F3:F18)
h. Create a filter for the above database.
 V-Lookup, H-Lookup
SL.
Salesperson’s Name Region Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
No.
1 Ram South Delhi 9277 16899 8924 6563 8291 13131 8114 6085 18540 13054 14585 5596

2 Jone East Delhi 16126 5058 6032 14714 5026 9363 5812 10689 17220 11565 14349 17580

3 Sohan North Delhi 13448 16989 14509 14819 10931 6579 13663 7319 9268 12082 19625 18278

4 Rohan West Delhi 18156 16989 19080 18325 8629 19812 17875 15494 11321 18204 8120 10750

5 Sita South Delhi 9638 5767 12090 14004 5943 18749 15432 14609 10612 19363 17923 17884

6 Sita South Delhi 19151 14272 9206 16996 15850 13568 18344 12192 12749 10645 19553 14652

7 Ramesh North Delhi 11488 15081 15940 13082 15242 15762 6952 7442 12123 7439 5292 18258

8 Suresh West Delhi 18718 8630 15896 9504 17503 15480 15486 19296 5477 11336 9409 15431

9 Kamal North Delhi 7125 6054 11238 14690 15237 8011 9298 12283 13300 8368 16004 17515

10 Mohit East Delhi 15892 8543 14486 11464 6136 17462 18524 6250 18905 11248 19464 7813

Function we Learn
1. V- Lookup: Use VLOOKUP when you need to find things in a table or a range by row.
E.g. =Vlookup(lookup value, Table array, Colum index no.)
=VLOOKUP(A18,A2:H12,8)
Use H-Lookup Respectively
Use Slicer & Pivot Table.
 Shortcuts Keys

Shortcut Description

Ctrl + N Create a new workbook.

Ctrl + O Open an existing workbook.

Ctrl + S Save the active workbook.

F12 Save the active workbook under a new name, displays the Save as dialog box.

Ctrl + W Close the active workbook.

Ctrl + C Copy the contents of the selected cells to Clipboard.

Ctrl + X Cut the contents of the selected cells to Clipboard.

Ctrl + V Insert the contents of the Clipboard into the selected cell(s).

Ctrl + Z Undo your last action. Panic button :)

Ctrl + P Open the "Print" dialog.

 Shortcut Description

Ctrl + F1 Show / hide the Excel Ribbon. Hide the ribbon to view more than 4 rows of data.

Ctrl + Tab Switch to the next open Excel workbook.

Ctrl + PgDown Switch to the next worksheet. Press Ctrl + PgUp to switch to the previous sheet.

Ctrl + G Open the "Go to" dialog. Pressing F5 displays the same dialog.

Ctrl + F Display the "Find" dialog box.

Home Return to the 1st cell of the current row in a worksheet.

Ctrl + Home Move to the beginning of a worksheet (A1 cell).

Move to the last used cell of the current worksheet, i.e. the lowest row of the rightmost
Ctrl + End
column.
 Shortcut Description

Select the entire worksheet. If the cursor is currently placed within a table, press once to
Ctrl + A
select the table, press one more time to select the whole worksheet.

Ctrl + Home then Ctrl + Shift + End Select the entire range of your actual used data on the current worksheet.

Ctrl + Space Select the entire column.

Shift + Space Select the entire row.