Excel Practical 1 - johnsjc



III. - Excel PracticalEX:1. Enter the following data in an Excel worksheet and save it salary.xlsx.Sl.NoEmployee NameBASICDAHRACCAGROSSPFITDEDNET1Abinau Bindra100002000180084014640131814642782118582Madhu Chapra95001900171079813908125213912643112653Gokul125004Lindsey98005Kevin68206Komal98507Beena45208Lakshmanan65809Gandhi985010Donald655011Peiterson450012Longfellow950013Kavitha750014Priya650015Willson850016Murugan750017Ganesan775018Moorthy485019Lokmanya985220Patel1245021Steffi1165022Fiorina1175023Gangadhar1250024Krishnan650025Mohan750026Oprah950027Hemelatha850028Uma500029Zeenath900030Barath120001. Fill the BASIC, DA, HRA, CCA, GROSS, PF, IT, DED, NET column using the following guidelines. a) DA is equal to 20 % of Basicb) HRA is equal to 15% of Basic+DAc) CCA is equal to 7 % of Basic+DAd) Gross =Basic+DA+HRA+CCAe) PF is equal to 9% of Grossf) IT is equal to 10% of Grossg) DED is equal to PF+ITh) Net = Gross-Ded2. Copy the entire table to a word document and save it as salary.doc3. Copy the entire worksheet to another worksheet .4. Name the first worksheet as salary1 and the second worksheet as salary25. Go to salary1 worksheet make changes in the formula as belowa) DA is equal to 15 % of Basicb) HRA is equal to 12% of Basic+DAc) CCA is equal to 5 % of Basic+DAd) PF is equal to 5% of Grosse) IT is equal to 12% of GrossEX: 2. Enter the following data in an excel worksheet and name it as cia.xlsxD.NOMID100MID(35)END100END(35)ASN(25)ATTNTOT(100)05UCO201A2B2C221305UCO202264211505UCO203112715405UCO204364912405UCO205122813405UCO206284314205UCO207687821305UCO208253119105UCO210373313205UCO212283812505UCO21327139405UCO214452913405UCO21517248105UCO216775721205UCO217105815405UCO218364210305UCO219716718505UCO220776620205UCO221427312305UCO222314211505UCO223596319505UCO22414166405UCO225214614305UCO226505018305UCO22771576105UCO229254613405UCO230172361Fill the MID(35) and END(35) column converting the marks into 35 equivalent and find the total.=(a2*.35) Find out how many rows and columns are there in a worksheet using.Practice using arrow keys, Tab, shift +tab, home, end, ctrl+home, ctrl+end, F5, ctrl+G, PgUp and PgDn keys.Practice different methods of selecting cells using a) Shift+arrow keysb) using F8, c) by entering cell address, d) using mouse, e)using keyboardPractice: a) select entire row, b) select entire column, c) select entire sheet.Type the following in a word document and name it as course.doc=IF(H2>=40,"PASS","FAIL") ProductRegion1Region2Region3Dolls3501275650Truks13251370800Puzzles165015251100EX:3. Prepare the following table, find total for each product and for each region, also prepare a graphEX:4. Enter the following data in an excel worksheet and name it as cia.xlsxD.NOMID100MID(35)END100END(35)ASN(25)ATTNTOT(100)05UCO201182921305UCO202264211505UCO203112715405UCO204364912405UCO205122813405UCO206284314205UCO207687821305UCO208253119105UCO2103733132=IF(H2>=40,"PASS","FAIL")1. Fill the MID(35) and END(35) column converting the marks into 35 equivalent and find the total.2. Find out how many rows and columns are there in a worksheet using.3. Practice using arrow keys, Tab, shift +tab, home, end, ctrl+home, ctrl+end, F5, ctrl+G, PgUp and PgDn keys.4.Practice different methods of selecting cells using a) Shift+arrow keysb) using F8, c) by entering cell address, d) using mouse, e)using keyboard5. Practice: a) select entire row, b) select entire column, c) select entire sheet.EX:5. Create the following table in Excel and find the result using IF function =IF(AND(B2>=40,C2>=40,D2>=40,E2>=40,F2>=40),“PASS”,“FAIL”)For grade=IF(H2>=75,"Distinction",IF(H2>=60,"First",IF(H2>=50,"Second","Third")))NameTAMILENGLISHMATHSHISTORYSCIENCETOTALRESULTClassGanesan4045807890Moorthy5052545440Lokmanya8242522558Patel5825608978Steffi4587473958Ganesan8790894539Moorthy2945704048Lokmanya4539525949Patel9040696428 EX :6. Following are the sales of Maruti Vehicles in Tamilnadu. Product Region-wise SalesChennaiTrichyMaduraiKovaiMaruti-800200235250175Omni250275350325Alto400300350400Swift175150150200WaganoR200225175250Versa250200250200a)Prepare a table in Excel and use the following functions. SUM, AVERAGE, MIN, MAX for each product and for each region and insert a ‘comment’ in the appropriate cell. b) Also prepare appropriate graph.EX: 7. Sales of Maruti car in different regions of TN are given below, using Pivot table generate as many tables as possible.REGIONPLACENUMBERM800ALTOA_STARSWIFTOMNIECODZIRESouthMadurai30051020202052020EastKovai30492510101081045WestTrichy36542125404172012NorthChennai3055510502593060EastKovai3000420303081032SouthMadurai3045532201042110NorthChennai3048812102010320SouthMadurai304572101412212SouthNellai3025101023124414EastERODE3021120201051235WestTrichy3025230123081221Northvillupuram3028812452091340SouthPalayamkottai30303101210221520EastOoty303112201020101914WestTanjore302950303010161532WestTRICHY305020302020202023NorthChennai300310101015132324NorthChennai300810251616161525SouthMadurai300715151518451626EastERODE30171781420242030EX :8. Enter the following and do as instructed belowParticularsQuarter-1Quarter-2Quarter-3Quarter-4Opening Bank Balance:160,000Expenditures Wages:33,50022,50023,00023,400Electricity:8,5008,5009,50010,600Overheads:10,00012,00013,90016,800Materials:50,00065,00075,00075,000Total (expenditures)Sales Income:130,000132,900133,900134,100Profit for this quarterClosing bank balance:Enter in formulae to calculate 'Total (expenditures) for each quarter. (The sum of all the individual expenditures) Enter in formulae to calculate the 'Profit' for each quarter. (Sales income less Total expenditures) Enter in formula to calculate the closing balance for each quarter (The Opening balance plus the profits) Enter in formulae for the 'Opening balance' for each quarter after the first oneEx.9:NameTAMILENGLISHMATHSHISTORYSCIENCETOTALRESULTGRADEGanesan4045807890333PASSMoorthy5052545440250PASSLokmanya8242522558259FAILPatel5825608978310FAILSteffi4587473958276FAILGanesan8790894539350FAILMoorthy2945704048232FAILLokmanya4539525949244FAILPatel9040696428291FAIL=IF(AND(B2>=40,C2>=40,D2>=40,E2>=40,F2>=40),"PASS","FAIL")Ex.10:RESULT AND GRADE USING IF and AND CONDITIONS and Conditional FormatingD.NOMID100MID(35)END100END(35)ASN(25)ATTNTOT(100)ResultGRADEGRADE05UCO2011003510035255100PASSDistinctionDistinction05UCO2027827.34214.711558PASSSecondSecond05UCO203113.85279.4515432.3FAILThirdFAIL05UCO204238.05103.512427.55FAILThirdFAIL05UCO2057827.3289.813454.1PASSSecondSecond05UCO206289.84315.0514240.85PASSThirdThird05UCO2076823.87827.321375.1PASSDistinctionDistinction05UCO20860213110.8519151.85PASSSecondSecond05UCO210103.5124.221230.7FAILThirdFAIL=IF(H3>=40,"PASS","FAIL")=IF(H3>=75,"Distinction",IF(H3>=60,"First",IF(H3>=50,"Second","Third")))=IF(H3>=75,"Distinction",IF(H3>=60,"First",IF(H3>=50,"Second",IF(H3>=40,"Third","FAIL"))))Functions in ExcelPredefines and built-in formulas are called ‘functions’. A formula or a function always starts with an “=” symbol.Mathematical functionsStatistical functionsDate and Time functionsLogical functionsText functionsa) M Mathematical functions=SUM()=ROUND()=SQRT()=ABS()=TRUNC()b) Statistical functions=MAX()=MIN()=AVERAGE()=COUNT()=COUNTA()=COUNTBLANK()=COUNTIF()=SUMIF()c) Logical Functions – are used to make a decision based upon a value within the spreadsheet. It evaluates the value and makes decision. Upon checking, one value is returned, if the condition is true, otherwise a different value is returned if the condition is false.=AND()=NOT()=OR()=IF()d) Date and time functions – date should be entered in correct format, otherwise it will not be treated as date. Date is aligned right. The default date format in excel is month/day/year=NOW()=DAY()=MONTH()=YEAR()=TODAY()=WEEKDAY()e) Text Functions=CANCATENATE()This funcition joins several text strings into one text stringEg. CONCATENATE (“ I AM”, “BASHA”)=LEN()This is used to return the number of characters in a text stringEg: =LEN(“I LOVE MY COUNTRY”)=LOWER()This converts all upper case letters in a text string to lower caseEg: =LOWER(“ WHAT ARE YOU DOING?”)Eg: =LOWER(A31)=UPPER()This converts all lower case letters in a text string into upper case.Eg: =upper(“what are you doing?”)=TRIM()Removes all spaces from a text string except for single spaces between wordsEg: =TRIM(“I AM OK ARE OK?”)=PROPER()This capitalizes the first letter in each word of a textEg: =PROPER(“ are you going to college or film”?)Some Examples :COUNTIFThis function is used to count cells within a range of cells that meet a specified criterion.=COUNTIF(A1:A10,20)=COUNTIF(B8:B28,50000)=COUNTIF(B9:B29,">=50000")=COUNTIF(A10:A30,"LUCAS")SUMIFThis function is used to return values from a specified range that meets the condition/criteria.=SUMIF(B8:B29,50000)=SUMIF(B9:B30,">50000")=SUMIF (A10:A30,"LUCAS")YEAR=YEAR()This function returns the year of particular cellIg. =YEAR(A3)MONTH=MONTH()This function returns the MONTH of particular cell/date=MONTH(A3)Day=DAY()This function returns the DAY of particular cell=DAY (A3)=TODAY()This function fetches the current date (of the system)=NOWThis function returns current date and time=WEEKDAY()This returns the weekday, in numberTo find out weekday(in words), using IF….=IF(A6=1,"Sunday",IF(A6=2,"Monday",IF(A6=3,"Tuesday",IF(A6=4,"Wednessday",IF(A6=5,"Thursday",IF(A6=6,"Friday","Saturday"))))))=IF(A6=1,"Sunday",IF(A6=2,"Monday",IF(A6=3,"Tuesday",IF(A6=4,"Wednessday",IF(A6=5,"Thursday",IF(A6=6,"Friday",IF(A6=7,"Saturday","")))))))Logical FunctionThey are used to make a decision based upon a value within the spreadsheet/worksheet. It evaluates the value and makes decisionOne value is, if the condition is true, otherwise a different value is returned if the condition is false.AND() - This returns value true if all the arguments are true and returns false, if one or more argument is false.NOT() – This reverses the value of its argument.OR() – This returns true, if any argument is true and returns false only when all arguments are false. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download