Scribd Logo
Upload

Welcome to Scribd!

  • 622 views

    MS Excel Assignment Mycslab 01

    Uploaded by

    raihanrobin2002
    The document contains information about student marks in different subjects and questions about calculating totals, averages, counts and grades. It asks to: 1) Find totals, averages and grades for each student. 2) Count number of students with different grades. 3) Find totals and averages for specific students using SUMIF. 4) Count students based on marks in Hindi and English subjects.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    MS Excel Assignment Mycslab 01

    Uploaded by

    raihanrobin2002
    622 views11 pages
    The document contains information about student marks in different subjects and questions about calculating totals, averages, counts and grades. It asks to: 1) Find totals, averages and grades for each student. 2) Count number of students with different grades. 3) Find totals and averages for specific students using SUMIF. 4) Count students based on marks in Hindi and English subjects.

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains information about student marks in different subjects and questions about calculating totals, averages, counts and grades. It asks to: 1) Find totals, averages and grades for each student. 2) Count number of students with different grades. 3) Find totals and averages for specific students using SUMIF. 4) Count students based on marks in Hindi and English subjects.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    622 views11 pages

    MS Excel Assignment Mycslab 01

    Uploaded by

    raihanrobin2002
    The document contains information about student marks in different subjects and questions about calculating totals, averages, counts and grades. It asks to: 1) Find totals, averages and grades for each student. 2) Count number of students with different grades. 3) Find totals and averages for specific students using SUMIF. 4) Count students based on marks in Hindi and English subjects.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 11

    Assignment -1

    Use of Formulas Sum, Average, If, Count,Countif & Sumif


    Roll No Student N Hindi English Math Physics Chemistry Total Average Grade
    1 RAM 20 10 14 18 15 77 15.4 A
    2 ASHOK 21 12 14 12 18 ? ? ?
    3 MANOJ 33 15 7 14 17 ? ? ?
    4 RAJESH 15 14 8 16 20 ? ? ?

    5 RANJANA 14 17 10 13 18 ? ? ?
    6 POOJA 16 8 20 17 15 ? ? ?
    7 MAHESH 18 19 3 10 14 ? ? ?
    8 ASHUTOSH 19 20 7 14 18 ? ? ?
    9 ANIL 22 13 8 12 19 ? ? ?
    10 PREM 26 12 10 11 27 ? ? ?

    Q.1 Find the Total Number & Average in all Subjects in Each Student .
    Q.2 Find Grade Using If Function - If Average Greater >15 then "A" Grade otherwise "B" Grade
    Q.3 How Many Student "A" and "B" Grade Use of Countif
    Q.4 Student Ashok and Manoj Total Number and Average Use of Sumif
    Q.5 Count how many Students Use of Counta
    Q.6 How Many Student Hindi & English Subject Number Grater Then > 20 and <15 Use of Countif
    Total Number of students
    Total no. of A Grade Student
    Total no. of B Grade Student
    Total No. of Ashok and Manoj
    Average No. of Ashok and Manoj

    Total number of students where Total


    Marks in Hindi and english is >20 and
    <15
    Assignment -2
    Use of Formulas - Product, If, Counta, Countif, Sumif

    SRNO ITEMS QTY RATE AMOUNT GRADE


    1 AC 20 40000 800000 Expensive
    2 FRIDGE 30 20000 ?
    3 COOLER 15 10000 ?
    4 WASHING 14 15000 ?
    5 TV 18 20000 ?
    6 FAN 17 2000 ?
    7 COMPUTE 10 25000 ?
    8 KEYBOAR 5 250 ?
    9 MOUSE 25 100 ?
    10 PRINTER 30 12000 ?

    Q.1 Using of Product Fomula for Calculate Amount = Qty*Rate


    Q.2 How Many Items in a List
    Q.3 How Many Items qty Greate Then > 20 and Less Then <20
    Q.4 Calculate Item Computer Qty, Rate and Amount using Sumif Formula
    Q.5 If Items Amount is Greater > 500000, Then Items "Expensive" otherwise "Lets Buy it".
    Assignment -3
    Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup

    SUBJECT 1ST 2ND 3RD TOTAL


    HINDI 20 15 20 55
    ENGLISH 30 12 15 ?
    MATH 15 14 14 ?
    PHYSICS 12 17 17 ?
    CHEMISTR 14 18 18 ?
    HISTORY 16 25 20 ?
    GEO 18 21 22 ?
    BIO 17 23 13 ?
    BOTANY 20 25 25 ?

    Q.1 HOW MANY SUBJECT ?


    Q.2 HOW MANY SUBJECT 1 PAPER GREATER THAN 20 ?
    Q.3 On Entering SUBJECT ( Example HINDI, MATH & ENGLISH) display TOTAL NO. & GRADE in separate cell
    Q.4 IF AVE. GREATHER THAN 20 THEN "A", IF AVE. GREATEHR THAN 15 AVE. "B" OTHERWISE "C"
    Q.5 Display SUBJECT TOTAL /AVERAGE using vlookup
    Assignment -3
    of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup

    AVERAGE GRADE
    18.3333333333333 B
    ? ?
    ? ?
    ? ?
    ? ?
    ? ?
    ? ?
    ? ?
    ? ?

    Use of Counta
    TER THAN 20 ? Use of Countif
    I, MATH & ENGLISH) display TOTAL NO. & GRADE in separate cell Use of Vlookup
    ", IF AVE. GREATEHR THAN 15 AVE. "B" OTHERWISE "C"
    ing vlookup Use of Vlookup
    Assignment -4 (Salary Sheet)
    Use of Formulas - Sum, NestedIf, Counta, Countif, Sumif, Vlookup

    NAME DEPARTMEPOST BASIC DA 2.5% HRA 3.5% PF 1.5% TOTAL GRADE


    RAM COMPUTERMANAGER 5000 125 175 50 5250 D
    SHYAM COMPUTERSUPERVISO 8000 ? ? ? ? ?
    MANOJ COMPUTERPION 3000 ? ? ? ? ?
    POOJA ELECTRICAGUARD 6000 ? ? ? ? ?
    RAHUL ELECTRICACASHER 8000 ? ? ? ? ?
    RAKESH ELECTRICAACCOUNTA 9000 ? ? ? ? ?
    ASHISH FINANCE MANAGER 10000 ? ? ? ? ?
    MANISH FINANCE GUARD 5000 ? ? ? ? ?

    Q.1 HOW MANY EMPLOYEE IN COMPUTER, FINANCE, ELECTRICAL DEPARTUse of Countif


    Q.2 HOW MANY BASIC SALARY IN COMPUTER DFPARTMENT ONLY? Use of Sumif
    Q.3 MANOJ, ASHISH POST & GRADE Use of Vlookup
    Q.4 IF TOTAL SALALRY IS GREATER THEN 20000 THEN "A", IF TOTAL SALARY GREATER THEN 10000 THEN "B", OTHERWISE "C
    Q.5 HOW MANY EMPLOYEE IS MANAGER & GUARD? Use of Countif
    000 THEN "B", OTHERWISE "C"
    Assignment -5 (Sales Report)
    Use of Formulas - Sum, If, Counta, Countif, Sumif, Vlookup, Lookup

    SALESMAN JAN FEB MAR APR MAY JUNE SALES TARGET RESULT
    RAMESH 2000 1500 300 1400 1000 1400 7600 10000
    NOT ACHIVED
    RAKESH 5000 1200 500 1200 1200 2800 ? 12000 ?
    RAHUL 3000 800 1200 3000 1500 3500 ? 18000 ?
    POOJA 1000 900 1800 5000 1400 1200 ? 10000 ?
    MANOJ 500 1000 2300 8000 1700 1400 ? 12000 ?
    ASHOK 800 500 2400 1900 1800 1800 ? 10000 ?
    AJEET 1200 1400 1500 700 2500 7000 ? 12000 ?
    ALOK 1500 1800 1800 1800 300 1500 ? 10000 ?
    AMRIT 1800 2500 1700 1500 2800 1800 ? 12000 ?
    SURENDRA 200 3000 1900 1200 1500 3000 ? 10000 ?
    SHASHI 1600 1200 2000 800 1700 800 ? 10000 ?

    Q.1 How many salesman? Salesman Ajeet Targest & Result? Use of Counta and Vlookup
    Q.2 If Sales Greater Than Target Then Target Achived otherwise Not Achiv Use of If Function
    Q.3 Rahul Pooja & Ashok Targest & result? Use of Vlookup
    Q.4 How Many Salesman Achived Target. Use of Countif
    Q.5 Which Sales Man Jan Sales 2000, & Feb Sales is 2500? Use of Lookup Function
    NOT ACHIVED
    Assignment -6
    Use of Formulas - Counta, Countif, Sumif, Hlookup, Conditional Formatting

    Items Date Cost


    BRAKES 1/1/2016 800.00
    TYRES 12/5/2016 2000.00
    BRAKES 18/5/2016 500.00
    SERVICE 20/5/2016 800.00
    SERVICE 10/2/2016 1000.00
    WINDOW 8/5/2016 1000.00
    TYRES 10/5/2016 1200.00
    TYRES 25/5/2016 1500.00
    CLUTCH 10/7/2016 1800.00
    TYRES 10/1/2016 2000.00
    CLUTCH 15/6/2016 1500.00
    CLUTCH 12/1/2016 1000.00
    WINDOW 1/1/2016 1200.00
    WINDOW 10/5/2016 1500.00
    WINDOW 10/5/2016 1800.00
    BRAKES 10/5/2016 1000.00
    BRAKES 14/8/2016 1200.00
    TYRES 15/8/2016 1500.00
    WINDOW 20/8/2016 1800.00

    Q.1 HOW MANY ITEMS ?


    Q.2 HOW MANY BRAKE, WINDOW & TYRES HAVE BEEN BOUGHTS?
    Q.3 HOW MANY ITEMS COST IS >1000 & BELOW > = 1000?
    Q.4 HIGHLIGHT TYRES ITESM & 500 BETWEEN 2000 COST.
    Q.5 ITEMS COLOUMN IS 15, 18 & 20 ITEMS NAME?
    Q.6 Total Cost of Window and Brakes Items?
    Use of Counta
    Use of Countif
    Use of Countif
    Use of Conditional F
    Use of Hlookup
    Use of Sumif

    You might also like