## Excel Commands for 120/130 Lab

• =A4 puts the contents of the cell A4 into the current cell

=A4/A5 can do math with cell contents

=SUM(A4:A8) puts the sum of A4 through A8 into the current cell

=AVERAGE(B2:B9) puts the average of B2 through B9 in the current cell

=STDEV(B2:B9) … and the standard deviation

=SQRT(2)   or

=SQRT(C4) will square-root a number or another cell

Any text in a cell makes the entire cell contents text - i.e., "3 cm" is not a recognized number.

• ### To find the 90% confidence interval for a quantity:

•   =2*stdev(range)/sqrt(size),

where range is the range of cells which contain the multiple measurements, e.g. c1:c10,

size is the number of measurements, e.g. 10.

Note: do not use Excel’s confidence function; their algorithm does not account for a small number of multiple measurements.

• ### To graph data:

• Highlight your column of x-values,

then holding down <Ctrl>, highlight your column of y-values,

then select the Insert tab, then choose the Scatter icon, then select the scatter graph without connecting lines.

Select the Layout tab and then add a chart title, labels for the x and y axes, and any other formatting you desire by clicking the appropriate icons.

Right click on the graph, then choose Select Data, then highlight the series you want to check, then click Edit to make sure the x and y data values are the correct ones.

• You have to give Excel the data given by by xrange and yrange:

Highlight a 2x2 block of cells, type (do not click on a cell just start typing):

=linest(yrange,xrange,1,1), then hit <Ctrl-Shift-Enter>

yrange,xrange is the range of cells which contains the y- and x-values for the data you want evaluated with the linest command (typically it is the data on your graph), e.g. b3:b7.

NOTE: The Chart Wizard asks for your data x axis first, while the linest command asks for your data y-axis first, be careful!

y = mx + b is the best fit straight line for the set of data, and sem, seb are the respective standard errors and appear in your 2x2 table as:

 m b sem seb

Note: remember that the 90% confidence level is given by two times the standard error.

• ### To get a trendline (the best fit line) on your graph:

• Right click on one of the data points on your graph and select add trendline, click the Options tab and select “Display equation on chart”. Click OK and the best-fit line and the line’s equation will appear on your graph.

• ### To edit a graph:

• First, right click on the part of the graph you want to edit and choose the appropriate command from the pop-up menus:

Data Graphed Incorrectly: click on Source Data, then click the Series tab. Click the icon to the right of the Y Values box, and highlight the correct y-values on the spreadsheet.  Then click the icon again.  Do the same for the x-values in the X Values box.

Editing Series Name: click on Source Data, click the Series tab, and then enter the name in the Name box.

Change Graph Type: choose Chart type, and choose the Chart sub-type desired.

Lines Connecting Points: To add or delete a line connecting points, double click on a data point, under the Patterns tab, choose Automatic or None under the Line option.

Editing Labels: To edit a title or axis label, single click the text on the plot. Edit the text, clicking the mouse outside the graph when through.

Insert Labels or Gridlines: Choose Chart Options. Click the appropriate tab, edit text or click the appropriate box, and then click OK.

Hints for Printing: If the column widths are the standard (i.e., you haven’t changed them; Excel’s standard is 8.43 wide), then only everything through column I will print on a page, if you go past that to the right, then it will get cut off and print on a second page. Shrink graphs that are not on a sheet of their own so that they fit on a single page.  Also remember that row 52 is the lowest line on the first page.

Switching Between Charts and Spreadsheets on Different Sheets: You can go back and forth between graphs (plotted on their own sheet) and spreadsheets, or just between different spreadsheets, by clicking on the tabs at the bottom of the page (e.g. Sheet1 or Chart1).