Mac excel tutorial 08

# Mac excel tutorial 08 -...

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Plotting data (including least fit) using Microsoft Excel 07/08  B. Plotting data (including least square fit) using Microsoft Excel 07/08    B1:  Type the x‐values from CELL  C2 to CELL C10 and y values  from CELL D2 to CELL D 10.               B2:  Go to INSERT in the toolbar  Click command CHART  Chose XY(Scatter) under  CHARTS menu.  Click picture of XY Scatter  graph          B3:              Right‐click on black graph area (hold control and  click on one‐button macs) and select “Select Data”    2    B4:  Click ADD  Click on X‐VALUES field.    Drag mouse from CELL C2 to CELL C10.   Data range for x‐values will be  automatically entered in the X‐VALUES  field of the chart  Clear Y‐VALUES field first  Do same procedure for y‐values  Click OK.               B5:  If the “Formatting Palette is not on the right, go to the  “View” menu and select “Formatting Palette”  Select graph and “Chart Options” will appear in  Formatting Palette  Under Titles on Formatting Palette, select “Horizontal  (Category) Axis”          B6:  Type the label your x‐axis in the text box below.  Select vertical axis and repeat.  Select “Chart Title” and enter a title.  The plot for Spring Constant is finished.  You can click on the edges of the plot  so that you can resize it  3    B7:            Click on the data points in the plot  Go to CHART in toolbar  Click ADD TRENDLINE command  Select TYPE tab.  Chose LINEAR trend  Click OPTIONS tab  Select “Display equations on  chart” option                  B8:  Click OK or press enter  Final form of the plot with a least square fit is displayed.    Notice that the equation contains the information about the slope of the plot  and intercept of the plot with y‐axis                  4    C. Slope and intercept calculation using Microsoft Excel     C1:    C2:  Type the x‐ values from CELL  C2 to CELL C10 and y‐ values  from CELL D2 to CELL D10.   To calculate the slope, the intercept as well as their uncertainties for a given  set of x‐values and y‐values, LINEST function can be used.           C3:                C4:  Type the following:   =LINEST(D2:D10,C2:C10,,TRUE)   Notice that first y‐values and x‐values.   Press SIMULTANEOUSLY on   CTRL+SHIFT+ENTER   Note: If you don’t press the key simultaneously,  then the values returned will be only for Slope and  Intercept.     Select four cells as shown in the figure.   5    C5:     The values will return as shown in the figure.   CELL C11: SLOPE   CELL C12: UNCERTAINTY IN SLOPE   CELL D11: INTERCEPT   CELL D12: UNCERATINY IN INTERCEPT     Notice that the slope and the intercept values returned here are the same  ones for the least square fit done in the plot.                             6    D. Calculation of the sum/difference of two columns; second power of a column     D1:  To calculate the sum of the values in  COLUMN B, type in the CELL B8   =SUM(B2:B7)   press ENTER.   Total value for the values from CELL  B2 to CELL B6 will be returned in  CELL B8.         D2:  To do an algebraic calculation of any  given set of data, type in CELL B9   =B2+B3‐B4+B5‐B6‐B7   The value for this calculation will be  returned after pressing ENTER.         D3:  To calculate the power of the an  entry in CELL B2, type in CELL C2  the following:   =POWER(B2,2)   and then press RETURN.   The value returned in CELL C2 will  be the squared value of the one in  CELL B2 since we wrote “2” for the power.         7    D4:  ! ! #\$%! To do the same calculation for the rest of the values in COLUMN B, bring the  mouse the lower right corner of the CELL C2. When the mouse turns from a  thicker cross (figure 2a) into a thinner cross(figure 2b), by clicking the left  button of the mouse drag it down up to CELL C7 (figure 2c) and release the  mouse button. The values are returned. (figure 2d)   &'!('!)*+!,-.+!/-0/10-)2'3!4'5!)*+!5+,)!'4!)*+!6-01+,!23!789:;<!=>[email protected]!)*+!.'1,+!)*+!0'[email protected]*)! /'53+5!'4!)*+!7B99!7CD!E*+3!)*+!.'1,+!)153,!45'.!-!)*2/F+5!/5',,[email protected]+!C-%!23)'!-!)*233+5! /5',,[email protected]+!C?%>!?H!/02/[email protected]!)*+!0+4)!?1))'3!'4!)*+!.'1,+!([email protected]!2)!('A3!1I!)'[email protected]+!C/%!!-3(! 5+0+-,+!)*+!.'1,+!?1))'3D!&*+!6-01+,!-5+!5+)153+([email protected]+!C(%! ! ! ! ! ! ! ! ! ! ! ! [email protected]+!C-! ! ! ! ! ! [email protected]+!C?! ! ! ! ! !   ! ! [email protected]+!C/! ! ! ! ! ! ! [email protected]+!C(! ! ! ! ! !           "! ! 8    ...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online