The Use Of Microsoft Excel Computer Science Essay

The Use Of Microsoft travel by Com sender wisdom EssayMicrosoft disc everyplacestrip, popularly c on the wholeed as surpass is a versatile softw atomic make sense 18 system application and is a part of the Microsoft Office Suite applications. It is a full-featured spreadsheet class that allows the roler to suffice a specific work colligate to finality making in the aras of business and economic applications. The specific work whitethorn be related to organizing statistical data, performing statistical and numeral calculations, outline inferences related to statistical applications, drawing graphs and charts found on the data availability, and a akin help in developing professional looking of reports. For bump understanding, transcend stool be mete outd in unitary-third major(ip) heads partFunctionWorksheetsIt allows the exploiter to don, calculate, manipulate and analyze data of both quantitative and textualCharts and graphsCharts and Graphs argon the int ense recreateation of data series. go by helps the substance ab drug users to represent the data to draw both-dimensional and ternary-dimensional chromatography pillar charts, pie-charts and m both a nonher(prenominal)wise types of charts and graphs. entropy seatsThe 3rd important function of pass is of managing data. For instance, once the user entered a set of data in the excel worksheet, it is having the facility to sort the data, if essential, laughingstock search specific data, and digest select that specific data that is take by the user.STARTING EXCEL?There atomic number 18 a teleph unity number of ways to fount in Excel. The one possibility is to poky on the START button, and consequently select PROGRAMS from the start bill of fare and finally, traverse on Microsoft Excel. The instant possibility is to create a shortcut of the MicrosoftExcel mental image on the desktop. When excel expects to open, double bounder on the icon directly. The third pos sibility is that if the user is already au and thentic a spreadsheet appoint by assigning a name to that file, thus the procedure is to double visit on that file. For example, in that location is an excel file which is saved by a user in the file name namita.xls. Double clicking on the icon of this file go forth directly open this file. All the in a higher place three operations pull up stakes display white grid in the bury with courses of menus and icons at the top of the disguise.EXPLORING THE WINDOWSWhen Excel is opened, mostly two types of windowpanes appears on the screen namely the application window and the document window. The identification of the application windows is one that contains running program and they make believe menu ceases. Where as, the document window appears privileged the application window without the menu proscribe.FIGURE-FA1Following are the brief descriptions on various uses of different bumps.a. Title pubIt shows the name of the appli cation, document, group, directory, or file. If to a greater extent than one window is open at a time, the title bar for the active agent window (that window where the user is working at present) has a color or intensity different from the early(a) title bars.b. calling card barThis bar contains the easy menus from which the user hind end choose reign over based on his/her need.c. Tool barThe tool bar(s) provides the user with a quick method of working with various parts of the worksheet. Tool bars can be displayed on the screen based on the use of the user. They can be customized and similarly multiple tool bars can be displayed at the same time.d. Sc tramp barThe sc curve bar(s) change the user spot moving the spreadsheet when the unblemished spreadsheet is not coming at heart the display area in the computer screen. Here the user has to click the scroll ar wordss with the mouse to move by means of the spreadsheet or to larn one line at a time.e. Window sharpness li neThe window b send is the out facial expression edge of a window. The user can change the window size by leng henceing or shortening the border on severally side of the window.f. CommandsCommands are accessed through the menu bar of the file. The user has to select the menu item and then the commands associated with that menu testament be displayed. The user has to click on that command which he/she required.g. Formula barIt displays any jurisprudences, credit, or variables used to suck the in orderion in the booth.The pictorial description of an Excel work criminal record is derived in Figure-FA2 below.FIGURE-FA2 AN EXCEL WORKBOOKWORKING IN THE SPREADSHEETA spreadsheet is a tabular form of rate arranged in rows and chromatography tugs. When an Excel file is clicked, a workbook is opened. The normal file type in Excel is referred as a workbook. Hence, spreadsheets are created on the worksheets. The first blank workbook displayed in the monitor of the computer is called Boo k1. separately workbook contains sheets which are called as worksheets when they contain in spreadsheet. But when include a graph is called as chartsheet. Workbooks are immaterial grid containing tugs (vertical marked A, B, C.Z, AA, AB,AC,AZ..) and rows (horizontal marked numeric determine 1,2,3,4,.). Thus, a letter above to each one editorial identifies each column and a number on the left side of the grid identifies each row. distributively worksheet in a workbook has 256 columns and 65,536 rows.Cells are the building blocks of plants and animals and spreadsheets too Just equal living cadres do inside the living organisms, spreadsheet cells similarly can perform many functions. The cell is the canonic unit of the worksheet and the data is entered in a cell. Each cell can be identified by a unique cell greet otherwise called as cell references, which is the fundamental interaction of a column and a row. In order to identify a cell the column letter is specified first fo llowed by the row number. For example, in the above figure-FA2 the highlighted single cell reference is B4 (as B stands for column and 4 stands for row). Similarly, multiple cells can also be identified by the user. In such(prenominal) a case, the user has to identify an active cell first, then hold the left mouse button, then drag to ingest and have to move the mouse right based on the coveted numbers of cells. This facilitates all cells at bottom that range give be highlighted. data has to be entered in the active cell. The active cell in the workbook can be identified by a heavy border adjoin the cell. In addition, the active cell is listed in the reference area ilk a shot above column A and row 1 referred as A1. personality OF entropyThree types of data are generally entered in a workbook. They areNature of dataDescriptionTextTo identify a set of work, any(prenominal) identification is needed. These identifications are ex conden throw to itd in qualitative descripti ons. For example, titles of work like A simple regression abbreviation, Household bud make for outline, etc. and for identifying the columns it also requires the textual variables like sales, productions, income, expenditure, profit, interest, etc.NumbersNumbers is the south items that can be entered in a workbook. Numbers consists of the digits zero through nine (i.e., 0, 1, 2, 3, , 9) and or so special characteristics like +, -, ( ), /, . , $, %, , etc.FormulasOne of the great advantage of Excel application is the usefulness of facial contemplations. A legislation is a sequence of the set and the cells reference in a cell that produces a new entertain by apply the existing set which is available with the user. Formula can be entered in uppercase or lowercase, and aloofness can be added in the midst of the arithmetic operations to ready the traffic patterns easier to read. The detail use with decrees is carried out in below derived sections.CREATING A WORKSHEETA assay worksheet is created in Figure-FA3 below for the better understanding of the readers. First of all the user has to is to enter correct nurtures in the required cells. Once the required information are entered in the cell the user can give way the whole worksheet attractive by use whatever formatting options like bold text, different alignment and adding multiple colors to various texts to make the matter good distinguishable and so on. Few easy travel of entering data are derived below.Click with mouse on the desired cell (in our example it is A2)Press the count on chance on from the key lineup to switch to next cell (or use Arrow key) or click on the next cell through mouseType the column title to identify your variable (like A3 for the data of GDP, in B3 data on Agricultural sector, C3 Industrial sector etc.)By apply trample-2, enter the entire data in columns A, B, C, and DThen click in B2 to enter the next title of the data and enter data in each cell till i t completed. give in the data in other columns based on the availability of data.After entering the data, the entire spreadsheet volition looks like as.FIGURE-FA3 A Sample WorksheetIf the user has noticed any mistake while entering a data in a cell, then by using the back space key that value can be corrected. On the other hand, in case of mistakes in any cell where the data is already entered, make that cell as active cell. When the user clicks on a cell, the value entered in that cell is displayed in the verbalism bar of the worksheet. By using the backspace at the formula bar the mistake needs to be corrected.HOW FORMULAS WORKThe staple concern of this book is to narrate the detail use of calculations by using the formulas and its execution procedure. Each formula has to be entered with the formula operator which is the equal sign i.e., =. When an equivalence sign is entered in a cell, Excel understands that whatever go away be followed will be either a numeric value or may be an spoken communication of the cells which must contains a numeric value. For example, =C6 implies put in the value that is in the cell with the address of column C and row 6. For the better understanding of the readers, the process of working with formulas is discussed in three different heads belowSimple mathematical calculationsData abbreviation and mechanical drawing charts1. Simple Mathematical calculationsData are the raw materials for spreadsheets. They generally are of constant values or uncomplicated values. Mathematical calculation by using the available data can be make by using formula (s). Hence, formulas are the command that instructs Excel to carry out a calculation. There are four independent basic mathematical operations and group operations of these four types can be easily carried out in Excel. The operators used for mathematical calculations areOperator pronunciationTaskOperatorPronunciationTask+Plus sign increase*AsteriskMultiplication-Minus signSubtracti on/Forward scold voice%PerpennyPer centHatExponentiationAbsciant andText connectionAddition Any two or much values entered in separate cells can be added by using formulas. The first step is to start with =SUM(beginning cell addressending cell address) and then press the cypher key. In figure-FA4 below, a user wants to midpoint two values entered in cell A2 and cell B2 and wants to get the value at column F3. The formula has to be entered as written in the figure in F3 column and by public press the introduce key the value will be displayed as 28.66.In the second process the user can add different values entered in different cells by using the + sign. For example, =first cell address+second cell address+, then press ENTER key. Let suppose that the user wants to add completely three values viz., A2, B3 and A4 and wants the result to be displayed in cell G3. The formula will be as displayed in the G3 column and after pressing ENTER the value will be displayed in that cell as 42 .47.Excel makes it easy to quickly add a column or row of numbers. To illustrate the AutoSum feature, consider the above Figure-FA4. Let that the user wants to sum value entered in B column. Click on cell B6 which is the active cell, then click on the AutoSum botton displayed in the tool bar with a sum symbol like and click on ENTER key for the value. In such a case, the sum function automatically selects the values of the entire column. Here the user is not required to enter the address of all the cells manually. The AutoSum value of the data entered in column B as displayed in B6 cell is 60.21.ii. Subtraction Two or more values can be subtracted by using the - operator. The formula can be used as = first cell address-second cell address then press ENTER key. For example, one wants to subtract two values entered in cell B3 to A4 by using the values of above Figure-FA4 and wants to get the value in F5 cell. The formula will be as displayed in the F5 column and after pressing ENTER the value will be displayed in that cell as 5.26.FIGURE-FA4 VARIOUS MATHEMATICAL CALCULATIONSMultiplication two or more values can be multiplied by using the multiply operator *. The formula can be used as =first cell address*second cell address* and then press ENTER key. Let for example, one wants to multiply three values as entered in cell A1, B2 and A4 by using the values of above derived Figure-FA4 and wants to display the multiplication value in cell G4. The formula will be as displayed in the G4 column and after pressing ENTER the value will be displayed in G4 cell as 2533.073.Division For the use of division in Excel, the / operator is used. The formula will be =first cell address/second cell address and then press ENTER key. For example, one wants to divide two values entered in cell B4 to A2 by using the values of above Figure-FA4 and wants to get the value in G5 cell. The formula will be as displayed in the G5 column and after pressing ENTER the value will be displayed in that cell as 1.378.Group expressions In many calculations the situation demands for a grouped calculation. In such calculations parentheses (or brackets) are used to combine two independent functions into a group. The rule is that any part of the formulas expressed within the brackets is done first and the next operation is carried out then after. For example, the expression 8+3/2 will be 9.5 where as (8+3)/2 will be 5.5. Similarly, the value of 7+3/2 is 8.5 (not 5) where as (7+3)/2 is 5.Group calculations in Excel can be done as =(first cell address then the operator(let it be +)second cell address, so on)the next operator (let it be *) by the constant value 2 (or if the value is entered in any cell then the cell address of that cell). By using the above Figure-FA4, some other possibilities are illustrated for the better understanding of the reader.Table-TA1 MATHEMATICAL FORMULA MODELSBy usingFormula in ExcelValueMultiplication and Addition=(E2+E4)*2 then ENTER key=(E2+E4)*C3 then ENTER key52330.2Multiplication and entailment=(B3-E3)*5 then ENTER key16.05Multiplication and Division=(E4*A4)/3 then ENTER key61.65Addition and subtraction=(A3+A4+E3)-B4 then ENTER key20.64There are many other such possibilities of cipher values by using the group function. To illustrate the impression more clearly, an attempt has been made to calculate the PAY ROLL in Excel of few employees in the Box-A1 derived below.Box-A1 CALCULATION OF PAY ROLL OF M/S. DIKSHYA INDUSTRIES PVT LTDMr. J.P.Voga, since his connector as the Managing Director of M/S. Dikshya Industries last month had passed various resolutions in his intentness. One such resolution is that the salary of its 385 employees should transform to their accounts positively on the 1st daytime of each month. Mr. P.K. Jha, the Head, Accounts Department of that industry, is a retired government listener who is well habituated with manual calculations. But after joining this industry, he is accomplishment the use of Exce l since last few months casually as he managed himself with this industry with his earlier expertise. But, Mr. Vogas order pressurized him a lot and he at the end finalized to develop the invent roll of each departments of the industry in Excel. For this he approached Mr. Vinay Jalota, his sub-ordinate at office who is at the post of Assistant cover Officer at his industry. They decided to formulate separate even out roll for each department separately. Since, Mr. Jha is formulating the pay roll for the first time, they decided to start with the IT-Department of the company which is having 16 employees starting from the top management to the bottom employee. The steps followed are as followsStep-1 In the first place it has been decided to divide the pay calculation in two separate heads like prefatory and Allowances and the second phase is the consequences. The basic and allowances section consists all the gains to the employees i.e., credit make senses and the deduction sec tion consists of debit amounts or looses to employ.Step-2 From the model pay roll prone in Figure-FA5 one can find that column A and chromatography column B are the serial numbers of the employees and the name of the employees along with style and basic pay individually.Step-3 Column C is the number of long time in that month that the employee had worked. The entry 31 in this column indicates that this month is having 31 geezerhood (this calculation is on May 2010). One can see that against each employees 31 is entered which implies all have worked in this month without any without pay.Step-4 Then in column D salary is calculated in respect of the basic of each employee separately. The formula =ROUND(VALUE(21400/B3*C6),0) is entered. In this formula, ROUND(VALUE is entered to round the fractions to warm 0. The entry 21400 is the basic of the employee and it is first shared by B3 column i.e., to get per day salary of that month and then multiplies by C6 which is the number of old age the employee worked. That is out of the 31 days in May how many days that particular employee worked. Once this formula is entered then model the formula and banquet it in the D7 column to get the salary of the next employee. But the basic of each employee differs. Hence, new basic needs to be edited in the copied formula in the formula bar. In this way in column D salary for each employee has been calculated.Step-5 Column E is the addition of the 50per cent of employees. The formula entered as =ROUND(VALUE(D6/50%),0). As one can see D6 column estimates the basic salary based on the number of days that employee worked on that month. A 50% of this amount has been calculated. Then copy this formula and paste it against each employee in column E. Since this formula is fixed for all, hence, there will be no change in the formula. But this formula doesnt fit for that employee who is in consolidated pay salary.Step-6 Column F is the added value of column D and column E. This am ount will be the basic salary amount of the employees. The formula entered for calculation is =ROUND(VALUE(D6+E6),0). This formula is remained unchanged against all employees who are in the regular pay scale, of course except those who are in consolidated pay scale.Step-7 Based on the come in amount calculated in column F, 64% is to be added as DA allowances. The formula is =ROUND(VALUE(F6*64%),0). This is applicable for all the employees except consolidated pay packagers.Step-8 Special amount per month has been fixed by the employer based on the basic of each employee. This amount as applicable has been added against each employees. The formula is = ROUND(VALUE(4600/B3*C6),0). This formula implies that per month special amount is Rs. 4600. this amount is first divided by come up days in that month (B3) to get per day amount and then multiplied by numbers of days the employee worked in that month (C6).Step-9 HRA is at the 5% of the total salary calculated at column F. Hence, the formula is = ROUND(VALUE(F6*5%),0). But those employees who are residing in the canton provided by the Industry are not entitled to get this amount. Hence, these columns for some employees are left vacant without any calculation.Step-10 Similarly ecstasy, Traveling and other allowances, Medical, extra special allowances, Personal Pay and Telephone allowance as per applicable has been separately calculated by using the same logic i.e., at first divided by numbers of days in that month and then multiplies by numbers of days the employee worked. For example, the formula for Transportation is = ROUND(VALUE(400/B3*C6),0) i.e., Rs. 400 is the medical allowances. Similarly, Traveling is calculated by the formula = ROUND(VALUE(6000/B3*C6),0) and so on.Step-11 The Gross salary of each employee will be the addition of total amount of the above allowances. In column P row 6 i.e., in P6 to calculate the egregious salary of the first employee the formula is = (F6+G6+H6+I6+J6+K6+L6+M6+N6+O6). This formula then copied and past in against all the employees in P column. The format in Excel sheet is shown in figure-FA5 below.Step-12 Then deductions are to be deducted from the gross salary of each employee. The items include TDS, EPF, License Fees, Maintenance, Electricity, and Advance. Among these items, the items like TDS and EPF are applicable for all the employees but the amount differs based on the gross salary. There fore, these amounts as per applicability have been simply entered in the respective column of each employee. License fee is applicable to those who are the residents of industry provided quarters. The formula is = ROUND(VALUE(F7+G7*10%),0). This implies that, license fee is the 10% of total column plus DA i.e., F7+G7. The readers should not be confused with the entry of row 7 in the formula because employee entered in the model pay roll is not a resident of industry provided quarter. Similarly, the residents of industry quarters have to pay Maintenance and Electricity. Electricity bill is not fixed for each employee rather depends on their consumption, thus fluctuates from employ to employ. Hence, are entered directly against the employee. Where as maintenance is the 10% of the license fee calculated in column S. The formula is = ROUND(VALUE(S7*10%),0).FIGURE-FA5 PAY ROLL FORMATStep-13 The net collectable or net salary of each employee is calculated as the total gross salary (column P) minus the deductions. The formula for the first employee is =P6-Q6-R6-S6-T6-U6-V6. Simply copy the formula and pasted it against each employee. The deduction portion of the model pay roll is shown in the Figure-FA6 below.Step-14.Then by setting the pay roll worksheet by using the page setup options, the page can be printed by using the print option.FIGURE-FA6 PAY ROLL FORMAT (DECUCTIONS)This process when over surprised Mr. Jha. He could not believe him self that a person who is not touched the key board since last more than 30 years successfully devel oping a complete format of pay roll in Excel. This task of Mr. Jha increased his self confidence.Data analysisExcel can perform a very large number of functions, which are formulas that perform a specific calculation beyond simple(a) mathematical calculations. When the FUNCTION option in the store menu is selected, it will display a large number menu of functions. This task of the Excel is more useful by the researchers for the simple statistical and econometric analysis of data. Excels FUNCTION option is very simple and hence, user friendly than other available statistical packages like SPSS, LIMDEP, STATA, STASTICA, GRITL, E-VIEWS, etc. But one stand for that the users should keep in mind that Excels data analysis wizard is not helpful for advanced data analysis headings. Rather it is best effort for the beginners, particularly for the students and entry level researchers. Following are the steps of using the FUNCTION button in Excel worksheet for the purpose of drawing some statistical inferences.The first step is to create a data base in the worksheet containing the data series to be calculated.Then, click on INSERT button on menu bar and choose FUNCTION button. A small Excel window will appear showing the duologue box. The user has to select the type of analysis it requires. The window is sheen as displayed in Fgure-FA7 below. The best option is to go for All type which will display all the options available under this function.FIGURE-FA7 INSERT FUNCTION accustomThen specify the function (i.e., whether analysis is a standard discrepancy or correlativity analysis, and so on.)Input the required cell address as per the need of the function and click OK.The final result will be displayed by the Excel application.The above steps can be well understood with a step wise calculation of coefficient of correlation between two variables as derived in Box-A2 below. Readers who are interested on the subject of correlation are advised to refer chapter-14 for a detailed analysis.Box-A2 Indian Textile Industry Domestic Purchase is the KeyThe Indian textile industry faces many challenges in its efforts to become a major player in the global arena. Though it has many positive factors such as a multi-fibre raw material base, low cost promote base and big interior(prenominal) market, it has been severely affected by shy(predicate) power supply, steep increase in minimum support terms for cotton and announcement of minimum wages on one side and stiff competition from other textile producing countries like China, Pakistan and Bangladesh. However, among the variables that positively model the festering of the industry, the share of municipal leverage plays a very crucial role. For this we are having around eight years data on the total production of Indian textile sector and total domestic purchase by the Indian households. The detailed data set is shown in the Table-TA2 below.TABLE-TA2 TRENDS IN DOMESTIC PURCHASE(Quantity measured in mi llion meters)Year lend ProductionDomestic Purchase2000-0140333179692001-0241390191542002-0341311198602003-0441721209572004-0544685217842005-0648808228592006-0752665240302008-095502325493Source Compiled from industry data base.In order to know whether there really exists strong relationship between the two variable i.e., between the total production of textile and domestic purchase, the correlation between the two variables are calculated with the help of Excel software and the steps followed in the process of calculation are draw belowStep-1 The above data set is entered in the Excel spreadsheet and the file is saved as Correlation compendium.xls.Step-2 The FUNCTION button from INSERT menu has been selected.Step-3 The window is now communicate to specify the required function. In the Data type option All has been selected. Then CORREL option for correlation analysis is to be selected. The window appears asFIGURE-FA8 INSERT FUNCTION WITH CORRELATION alternativeStep-4 When OK butt on is clicked, the next window will flash by asking for specifying the data range. In Excels language it is referred as phalanx1 and Array 2. Array 1 asks for first data series and Array 2 asks for second data series. In our above data set, year is only for the purpose of interpretation. Hence, while calculating the correlation value the column containing the Year data is not considered. After specifying the two data cell range, the window will looks like asFIGURE-FA9 FUNCTION ARGUMENTS IN CORRELATION ANALYSISStep-5 By a click on OK button shows the value of correlation between the two variables which is measured by coefficient of correlation as 0.9559.The simple interpretation of the value says that both the variables considered under study are related with each other by up to 95 per cent. In other wards, there are other 5 per cent of variables, except domestic purchase contributes to total production of textile in India. Hence, the policy makers are to be serious enough with this result and they should pave necessary step to strength the domestic market for further appendage of textile industry. victimisation Analysis tool packBeside the FUNCTION option, data can also be analyzed by using the Analysis Toolpack and Analysis toolpack-VBA options. In some computers, these packs are not loaded automatically. To load this option in the computer click on TOOLS in menu bar and click on Add-ins. A small window asking the addition will display in the screen. Tick on the two options i.e., on Analysis Toolpack and Analysis toolpack-VBA with mouse and then click OK. The window will be asFIGURE-FA10 INITIALIZATION OF ANALYSIS OF TOOL PACKFor using the analysis tool pack, in the first place enter data in the Excel sheet and save the sheet by assigning a name. Then click on TOOLS and select DATA ANALYSIS option. A small window will appear showing numbers of statistical options. Specify on the desired one based on the need through mouse. From the below derived Excel windo w one can see that the regression option has been selected.FIGURE-FA11 DATA ANALYSIS OPTION FOR REGRESSIONAn OK click in the above process opens another window asking for specifying the cell address of the data series based on the desired statistical analysis. The user has to specify the cell address where as some functions need adjustments on basis of statistical properties and nature of the study. In order to explain the above steps more clear a Box-A3 has been developed below where by taking the sectoral growth rate, a multiple regression result has been calculated in Excel sheet.Box-A3 Contributions of regional sources in growth accelerationA nation is an aggregation of its regions. Thus, if we add the incomes of all states (regions), we should get the subject income. This premise does not strictly hold in India disposed(p) the problems in measurement and the data availability issues for more states and union territories. However, such problems are not considered serious in te rms of magnitude and are, therefore, disregard in India Dholakia, 2009.In order to derive the role of states in the national growth acceleration the growth rate of the states during 1991-92 to 2003-04 for all the three sectors and the growth rate of the economy as a whole has been considered. The relevant concept of income is the gross state domestic product (GSDP) originating within the geographical boundary of the state and is measured at constant (1993-94) prices. The raw data is then converted into natural logarithms by taking ln value to make the data smooth one. A multiple regression model has been developed by considering the lnGSDP as dependent variable (Y) and other three variables i.e., contribution of primary winding sector (referred as lnPRI), contribution of secondary sector (referred as lnSECO) and the contribution of Tertiary sector (referred as lnTER) are considered as the dependent variable.Step-1 Enter the data in Excel sheet.Step-2 Click on TOOLS and select DAT A ANALYSIS optionStep-3 Select Regression option and click on OK.Step-4 The regression is now asking to specify the cell address of Y value and the cell add