Excel Tutorial

Leroy E. Laverman

Informations

• Publié par: Obbe
• Nombre de lectures: 37
• Langue: English

Extrait

Excel Tutorial Below is a very brief tutorial on the basic capabilities of Excel. Refer to the Excel help files for more information. Working with Data Entering and Formatting Data Before entering data into a spreadsheet a little thought should be given as to the arrangement of the data.A typical use of Excel is to keep track of student grades.A convenient arrangement of data is to list the students names in columns and the various assignments in rows.The formatting of data (text, numbers, dates, currency etc..) can be set to your personal preferences.Select the data you wish to format and under theFormatmenu selectCells. The dialog box that comes up lists many different available formats.For instance you can choose to show only 1 decimal place for all calculations.Customizing the formatting can make it easier to look at the data.Individual cells are named according to which row and column they are in.By default excel labels columns with letters and rows with numbers.The data in the first column and first row is referred to asA1. Entering Formulas, Working with Data  Thepower of Excel comes from its ability to perform complex calculations on an active spreadsheet. This means that you can enter formulas for the manipulation of experimental data that update when the data changes.In order for excel to differentiate between a formula and text, the equal sign,=, isused to let the software know that what follows is a formula. All of the normal math functions are available. For example, if you wish to add 5 and 9 you would simple click on any cell and type=5+9, the cell would then display14. Orif you would like to multiply the value in cell A10 by the value in cell B12 you would enter=A10*B12. Parenthesis are used the same way you would use them in algebra. For example,=(A10*B12)+C2is different from=A10*(B12+C2). Arithmetic OperatorMeaning Example + (plus sign)Addition 3+3 - (negative sign)Subtraction 3-1 - Negation-1 (negative 1) * (asterisk)Multiplication 5*8 / (forward slash)Division 7/3 % (percent)Percent 20% ^ (carat)Exponentiation 4^3(i.e. 4 to the third power) E (or e)Scientific Notation3.1E-3 ( i.e. 0.0031) Sqrt() Squareroot =sqrt(9.1) π , (3.1416....) Pi =PI() sin(), cos(), tan(), etc..Trigonometry functions* =sin(2*PI()) (i.e.sin(2π)=0) *Note: The default angles are in radians.

A basic task is the ability to select individual data points or data ranges listed in other cells. Data are referenced in formulas according to the table below. To refer toEnter The cell in column A and row 10A10 The range of cells in column A and rows 10 through 20A10:A20 The range of cells in row 15 and columns B through EB15:E15 All cells in row 55:5 All cells in rows 5 through 105:10 All cells in column HH:H All cells in columns H through JH:J The range of cells in column A though E and rows 10 through 20A10:E20 The above reference formats arerelative. That is, if you copy a formula containing the referenceA10the next to column to the right the reference in the formula is changed toB10. If you want a reference to beabsolute(i.e. one that doesnt change when you copy the formula to another location) us the reference$A$10. Functions Statistical analysis of student grades and experimental data sets can provide useful information.For example, if we have a data set in column A that consists of 15 measurements.We can obtain the following values: Value desiredFormula to enter mean =average(A1:A15) median =median(A1:A15) standard deviation=stdev(A1:15) confidence interval*=confidence(alpha, s, n) number of data points=count(A1:A15) highest value=max(A1:A15) lowest value=min(A1:A15) range =max(A1:A15)-min(A1:A15) * In the formula for confidence interval alpha isequal to 0.05 for a 95% C.L. 0.1 for a 90% C.L. and so on, s is the standard deviation of the data and n is the number of data points.Note: The confidence interval assumes that you have the population standard deviation (i.e. infinite degrees of freedom). Do not use this function to calculate confidence intervals in the chemistry 116AL course. Note we could also use the reference (A:A) as follows =average(A:A) to return the average of all of the numbers in column A, not just the numbers in cells 1 to 15. Using these basic functions we can calculate the relative standard deviation (RSD)(standard deviation / mean) and the coefficient of variance (COV) (RSD * 100).

Advanced Data Analysis Excel will automatically calculate several common statistical values using the data analysis add-in.This add-in is not installed by default.To install theAnalysis ToolPakperform the following steps. 1.On theToolsmenu, clickAdd-Ins 2.Select theAnalysis ToolPakcheckbox 3.ClickOK To use the tools perform the following 1.On theToolsmenu, clickData Analysis 2.In theAnalysis Toolsdialog box, click the tool you wish to use (for exampledescriptive statistics) 3.Enter the requiredInput RangesandOutput Ranges 4.ClickOK Students frequently wish to compare their own grades to the class.A graphical representation of this is the Histogram plot.The data analysis tools makes this task easy.A histogram is a plot of the frequency vs. a category. For grades the categories will be the various score ranges.Excel denotes histogram categories asbins. You must set up your bins before making a plot.For example, if you have a maximum of 100 points possible for a lab report you may choose to group your scores by tens.Choose an empty column and enter the following values: 0, 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100. After setting up your bins, perform the following steps to generate a histogram plot. 1.On theToolsmenu, clickData Analysis 2.In theAnalysis Toolsdialog box, clickhistogram 3.ClickOK 4.Enter the cell range for your data in theInput Range:section (e.g. A1:A15) 5.Enter the cell range for your bins in theBin Range:section (e.g. B1:B11) 6.Select theOutput Range:button and enter a reference for a single empty cell(e.g. D1) 7.Select theChart Outputcheck box 8.ClickOK The resulting plot should look somethinlike the followin:

Plotting in Excel Basic Plotting Arrange your data so that the x values are in one column and your y values are in another.For example x values are in column A1:A50 and the y values are in column B1:B50.Select this data range, i.e. click and drag your mouse to select the range A1:B50.Perform the following to make a plot. 1.From theInsertmenu, clickChart 2.From the chart wizard dialog box selectXY (Scatter) 3.Pick the type of plot you would like (e.g. symbols, lines or both) 4.clickNext(a preview of your plot will be shown) 5.clickNext 6.Insert the appropriate labels for theValue (X) axis,Value (Y) axisandChart Titlefor your chart (you may leave any or all of these fields blank) 7.ClickFinish Your plot will be inserted into you spreadsheet. Many items can be customized on the plot, including the marker sizes, shapes and colors and line styles, widths and colors. Ifyou right click on one of your data points and selectFormat Data Series, a dialog box will come up with options to change all these attributes.Alternatively you can click on one of your data points to select them and from theFormatmenu selectSelected Data Seriesto bring up the same options box. Adding a Least Squares Fit to your data Often times a best fit to a line is need for data analysis.This can be accomplished with Excel as well.For example we have the following data plotted as described above and we wish to fit this data to a straight line.

Click on the chart to select it and from theChartmenu selectAdd Trendline. A dialog box will come up asking what type of fit you would like.Several options are available, chooseLinear. Clickon theOptionstab and click on theDisplay Equation on ChartYou can forecast forward or backwards if you would like to extend thecheck box. line past the range of your data.For example, the above chart is forecast forward by 5 units and backward by 0 units, shown below.Note where the best fit line extends to.

Many times the slope and/or intercept value are needed in other calculations.These values can be obtained as a spreadsheet function for use in other calculations. As above lets assume we have our x values in column A1:A50 and our y values in column B1:B50. Desired ValueEnter slope =slope(B1:B50,A1:A50) y-intercept =intercept(B1:B50,A1:A50) The resulting values can now be used in further calculations as required.