MS excel Practical notes RCSAS Naoholia

Table no: 1

	A	B	C	D
1	items	Unit	Price	Sales
2	Lux	5	17.25
3	Cinthol	12	10.00
4	ganga	10	9.00
5	camay	3	12.16
6	margo	5	15.75
7	lifebuoy	8	12.00
8	santoor	12	8.25
9	moti	0	12.00

Q. Find:

sales

total sales

average sales

maximum sales

minimum sales

numeric cells

blank cells

Non-blank cells.

Solution:

To find sales- =b2xc2

Total sales- =sum(D2:D9)

Average sales- =average(D2:D9)

Maximum sales- =max(D2:D9)

Minimum sales- =min(D2:D9)

Numeric cells- =count(A1:D9)

Blank cells- =countblank(A1:D9)

Non-blank cells- =countA(A1:D9)

Table no: 2

1	BRANCH	MONTH	SALE
2	margo	jan	1000
3	avinjim	march	2500
4	udapi	feb	3200
5	margo	march	1800
6	margo	feb	3200
7	manglore	feb	1000
8	manipal	march	2000
9	banglore	march	3200
10	udapi	march	1000

Q. FIND:

Margo total

March total

Sum of sales above 2500

Count of manipal

Count of february

Count of sales above 3500

Solution:

Margo total- =Sumif(A2:A10,"margo",C1:C10)

March total- =sumif(B2:B10,"march",C2:C10)

Sum of sales above 2500- =sumif(C2:C10,">=2500")

Count of manipal- =countif(A2:A10,"manipal")

Count of february- =countif(B2:B10,"feb")

Count of sales above 3500- =Countif(C2:C10,">3500")

Table no: 3

A	B	C	D	E	F	G	H
Name	grade	basic	DA	HRA	GROSS	PF	NET
Arun	B
Marry	C
James	C
Ravi	A
Ganesh	B
Rajesh	C
Binayak	A
Pinku	A
Bijoy	B

Q: FIND:

Basic pay

DA (dearness allowance)

HRA (house rent allowance)

Gross

P/F (Provident Fund)

Net salary

Solution:

Basic pay- =IF(B2="A",2500,IF(B2="B",2200,IF(B2="C",2000)))

Dearness allowance- =C2*40%

House rent allowance- =C2*15%

Gross income- =SUM(C2:E2)

Provident Fund- =F2*4%

Net salary- =F2-G2

Table no: 04

	A	B	C	D	E	F	G	H	I	J
1	Names	English	Assamese	Social	G. sc	Hindi	Maths	Total	%age	Result
2	Xyz
3	Xyz
4	Xyz
5	Xyz
6	Xyz
7	Xyz
8	Xyz
9	Xyz

Q. Find:

Total marks of each students.

Total percentage.

Result.

Solution:

Total marks - =sum(B2:G2)

%age- =H2/6

Result- =if(i2>=60,"1^st",if(i2>=45,"2^nd",If(i2>=30,"3^rd","fail")))

Table no.5

Item code	Units	Price	Sales	Commission	Remarks
100	1000	75
101	145	10
102	2000	12
103	1000	02
104	1100	0.5
105	800	02
106	145	1.5
107	1100	2.25
108	1550	02
109	2000	03
110	2200	05

Commission table sales commission Less than 1000 0 From 1000-5000 2%of sales Above 5000 5%of sales

Find:

Sales of each items

Total sales

Highest Sales

Lowest sales

Average sales

Average price

Remarks Table
Sales	Remarks
Less than 1000	Poor
1000 to 1550	Good
Above	Excellent

Count of excellent

Count of good

Count of units above 1000

Count of price not equal to 2

Total commissions of excellent

Count of total sales of unit above 1000

Total commissions of goods

Solution:

Sales =B2*C2

Commission =IF(D2<1000,D2*0%,IF(D2<5000,B2*2%,D2*5%))

Remarks =If(D2<1000,"POOR",IF(D2<4550,"GOOD","EXCELLENT"))

Total sales =Sum(D2:D12)

Highest sales =Max(D2:D12)

Lowest Sales=Min(D2:D12)

Average sales =Average(D2:D12)

Average price =Average(C2:C12)

Count of excellent =Countif(F2:F12,"excellent")

Count of good =Countif(F2:F12,"good")

Count of units above 1000 =countif(B2:B12,">1000")

Count of price not equal to 2 =countif(C2:C12,"<>2")

Total commission of excellent =sumif(F2;F12,"excellent", E2:E12)

Total sales of units above 1000 =sumif(B2:B12,">1000",D2:D12)

Total commissions of goods =sumif(F2:F12,"Good",E2:E12)

Table no: 6

1	Name of the employee	HRA	DA	CCA	Gross salary	PF	Lic premium	Net monthly income
2	Santanu
3	Taznur
4	Montu
5	Nitul
6	Prasant
7	Amit
8	Ranjit
9	Mayan
10	Akashi
11	Alma
12	Amitabh
13	Mesha
14	Arunjyoti
15	Roshan

Find:

Create a new column for basic pay of each employee between "Name of the Employee" and "HRA"

Calculate the HRA(House rent allowance), which is 20% of the basic pay.

Calculate the DA(Dearness Allowance), which is 40% of the basic pay.

Calculate the CCA(Commentary city allowance)which is 10% of the Basic pay.

Calculate the Gross Salary which is the sum of the basic pay , HRA,DA,CCA.

Calculate the PF( Provident fund) which is 35% of the Gross salary

Calculate the Lic premium which is Rs.200/-(fixed).

Calculate the Net monthly income, which is the deduction of PF and LIC premium from gross salary.

Calculate the gross salary of Mr. taznur whose basic salary is 20% more than the basic salary of Mr.Santanu.

Calculate the Gross salary of Mr.montu, whose basic salary is 10% less than the basic salary of Mr. Taznur.

Calculate the Gross salary of Mr.Nitul whose basic salary is 50% less than the basic salary of Mr. Santanu.

Others basic salary is 45% less than Mr. Santanu.

Display the names of the employee whose name begins with letter "A". by using Autofilter

Delete the record of Mr.Roshan using Data.

Save the modification and exit Ms-excel.

Solution:

HRA =B2*20%

DA =C2*40%

CCA =C2*10%

Gross Salary =SUM(C2:E2)

PF =G2*35%

LIC =200

Net Monthly Income =G2-H2-I2

Mr. Taznur =C2+C2*20%

Mr. Montu =C2-C2*40%

Mr. Nitul =C2-C2*50%

Others =C2:C2*45%

Select all Table → Click on Data tab → Filter → Click the arrow head "▼" → Next to Basic Pay → Number Filter →Greater than → Is greater than → 5000 → OK

Select all table → Click on Data tab → Filter → Click the arrow head "▼" → Next to name of the Employee → Text Filter → begin with →

Select all Table → Click on Data tab → Filter → Click the arrow head "▼" → Next to name the employee → Uncheck Roshan → OK