EXERCISE:1(A)
CLEANME SOAP CENTRE
ITEM UNITS PRICE SALES
LUX 5 8.25 =B3*C3
CINTHOL 12 8 =B4*C4
GANGA 10 7.5 =B5*C5
CAMAY 3 9 =B6*C6
MARGAO 5 8.6 =B7*C7
LIFEBUOY 8 6 =B8*C8
SANTOO
R 12 6.5 =B9*C9
MOTI 0 12 =B10*C10
TOTAL SALES =SUM(D3:D10)
AVERAGE PRICE =AVERAGE(C3:C10)
MAXIMUM PRICE =MAX(C3:C10
MINIMUM PRICE =MIN(C3:C10) (B)
NUMERIC CELLS =COUNT(A1:D10)
NON BLANK CELLS = COUNTA(A1:D10)
BLANK CELLS =COUNTBLANK(A1:D10) EXERCISE:2
EXERCISE:3
NAME AGE BASIC DA HRA GROSS PF NET
ARUN 25 1500
MARY 21 1400
JAMES 19 2200
RAGHAV 24 2100
GANESH 29 2500
RAJESH 25 1800
VINAYA
K 32 3000
GIRISH 24 2500
MAHES
H 19 2200
RAVI 22 2000
1. IN COLOUMN D, CALCULATE DEARNESS ALLOWANCE @ 40% OF BASIC IN CELL D2 TYPE
=C2*40% AND COPY THE FORMULA TO OTHER CELLS.
2. IN COLOUMN E, CALCULATE HOUSE RENT ALLOWANCE @10% OF BASIC.
3. IN COLOUMN F, CALCULATE GROSS SALARY BASIC+DA+HRA.
4. IN COLOUMN G, CALCULATE PF @4% OF GROSS.
5. IN COLOUMN H, CALCULATE NET SALARY GROSS-PF.
6. INCLUDE 2 DECIMAL TO PF AND NET AMOUNT.
7. INSERT THE COLOUMN AT THE BEGINNING (A) AND TYPE SERIAL NUMBER FOR DIFFERENT
EMPLOYEES.
8. INSERT THE ROW AT THE TOP AND TYPE THE HEADING, BEST PROVISION STORES AND
CENTER IT IN BETWEEN COLOUMN AND COLOUMN I.
9. FORMAT THE MAIN HEADING AND COLOUMN HEADING INCLUDING BOLD, WITH FONT SIZE
14, CELL COLOR YELLOW, FONT COLOR BLUE.
10. INCREASE OR DECREASE THE COLOUMN WITH IF NECESSARY.
11. SAVE THE WORKBOOK UNDER THE NAME BESTPRO
EXERCISE:4
BRANCH MONTH SALES
MARGAO JANUARY 1000
PANJIM MARCH 2500
UDUPI FEBRUARY 3200
MARGAO MARCH 1800
MANGALORE FEBRUARY 3200
MANIPAL FEBRUARY 4100
BANGALORE MARCH 2100
UDUPI MARCH 3200
MANIPAL MARCH 1800
MARGAO TOTAL =SUMIF(A2:A10,"MARGAO",C2:C10)
MARCH TOTAL =SUMIF(B2:B10,"MARCH",C2:C10)
SUM OF SALES ABOVE 2500 =SUMIF(C2:C10,">2500")
SUM OF SALES TILL 2500 =SUMIF(C2:C10,"<=2500")
COUNT OF MANIPAL =COUNTIF(A2:A10,"MANIPAL")
COUNT OF FEBRUARY =COUNTIF(B2:B10,"FEBRUARY")
COUNT OF SALES ABOVE 3000 =COUNTIF(C2:C10,">3000")
ITEM CODE UNITS PRICE SALES COMISSION REMARKS
100 1000 5.50
EXERCISE:5
101 1200 6.75
102 1300 2.00
103 800 4.00
104 1200 5.00
105 1800 0.50
106 145 2.00
107 1900 3.50
108 1550 2.25
109 2000 2.00
110 2200 3.00
TOTAL SALES
HIGHEST SALES
LOWEST SALES
AVERAGE SALES
AVERAGE PRICE
COUNT OF EXCELLENT
COUNT OF GOOD
COUNT OF POOR
COUNT OF UNITS >1000
COUNT OF PRICE <> 2
TOTAL COMMISSION OF EXCELLENT
TOTAL COMMISSION OF GOOD
1. CALCULATE THE VALUE OF SALES (UNIT*PRICE) IN COLOUMN D.
2. CALCULATE TOTAL SALES AT THE BOTTOM OF COLOUMN D.
3. CALCULATE HIGHEST, LOWEST AND AVERAGE SALE VALUE.
[Link] THE COMMISSION GIVEN TO THE SALESMAN FOR EACH ITEM, DEPENDING ON THE SALES
VALUE OF EACH ITEMN IN COLOUMN E.
SALES COMMISSION
LESS THAN 1000 0% OF SALES
1000 TO 5000 2% OF SALES
ABOVE 5000 5% OF SALES
[Link] IS DECIDED BASED ON SALES IN COLOUMN F
SALES REMARKS
LESS THAN 1000 POOR
1000 TO 4550 GOOD
ABOVE 4550 EXCELLENT
formulas
Commission:
=if(d2<1000,d2*0%,if(d2<=5000,d2*2%,d2*5%))press enter
.
OR
=if(d2<1000,d2*0%,if(d2<=5000,d2*2%,if(d2>5000,d2*5%))
) press enter.
Remarks:
=if(d2<1000,”poor”,if(d2<=5000,”good”,”excellent”)) press
enter.
OR
=if(d2<1000,”poor”,if(d2<=5000,”good”,if(d2>5000,”excelle
nt”)))press enter.
KASTURBA MEDICAL HOSPITAL
PATIENT DEPT WARD ADDMISSION DISCHARGE NO OF DAYS ROOM RENT FEES AMOUNT
KAJON SKIN SPECIAL 1-Jan-99 10-Jan-99 1200
AJAY EYE GENERAL 1-Jan-99 5-Jan-99 2100
MADHURI NOSE SPECIAL 2-Jan-99 2-Mar-99 800
AKSHAY EAR SPECIAL 2-Feb-99 2-Feb-99 900
SHARUKH SKIN GENERAL 4-Feb-99 5-Feb-99 1350
GOVINDA EYE GENERAL 22-Feb-99 28-Feb-99 2500
SUNNY EAR GENERAL 3-Mar-99 31-Mar-99 2000
URMILA EAR SPECIAL 10-Mar-99 5-May-99 4150
SANJAY SKIN SPECIAL 18-Mar-99 1-Apr-99 3100
EXERCISE:6
1. CALULATE THE NUMBER OF DAYS I.E (DISCHARGE DATE- ADMISSION DATE) +1
2. CALCULATE THE ROOM RENT BASED ON THE WARD.
WARD ROOM RENT PER DAY
SPECIAL 125
GENERAL 50
3. AMOUNT IS ROOM RENT + FEES
4. SORT THE DATABASE IN THE ASCENDING ORDER OF DEPARTMENT. REMOVE THE SUBTOTAL
5. CREATE A SUBTOTAL OF FEES COLLECTED FROM EACH DEPARTMENT. REMOVE THE SUBTOTAL.
6. USING SUBTOTAL, DISPLAY THE NUMBER OF PATIENTS IN EACH DEPARTMENT. REMOVE THE
SUBTOTALS.
7. USING AUTO FILETR, DISPLAY THE RECORD OF GENERAL WARD.
8. USING AUTO FILTER DISPLAY THE RECORD OF PATIENTS, WHOSE NAME BEGINS WITH S.
9. USING AUTO FILTER DISPLAY ANY 3 RECORD OF PATIENTS WHO ARE HOSPITALISED FOR
HIGHEST NUMBER OF DAYS.
[Link] ADVANCE FILTER, DISPLAY THE PATIENT, DEPT, WARD AND NO OF DAY IN EAR
DEPARTMENT AND IN SPECIAL WARD IN SEPARATE PLACE OF WORKSHEET
7.
1. Find the total marks obtained by students using suitable formula.
2. Obtain the result whether the student is passed or failed.
If student gets marks less than 32 in any subject then, the result is failed otherwise
passed.