Mac excel tutorial 08

Mac excel tutorial 08 -...

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

View Full Document Right Arrow Icon
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:;<!=>!?523@!)*+!.'1,+!)*+!0'A+5!52@*)! /'53+5!'4!)*+!7B99!7CD!E*+3!)*+!.'1,+!)153,!45'.!-!)*2/F+5!/5',,!G42@15+!C-%!23)'!-!)*233+5! /5',,G42@15+!C?%>!?H!/02/F23@!)*+!0+4)!?1))'3!'4!)*+!.'1,+!(5-@!2)!('A3!1I!)'!7B99!7J!G42@15+!C/%!!-3(! 5+0+-,+!)*+!.'1,+!?1))'3D!&*+!6-01+,!-5+!5+)153+(D!G42@15+!C(%! ! ! ! ! ! ! ! ! ! ! ! K2@15+!C-! ! ! ! ! ! K2@15+!C?! ! ! ! ! ! 
 ! ! K2@15+!C/! ! ! ! ! ! ! K2@15+!C(! ! ! ! ! ! 
 
 
 
 
 "! ! 8
 
 ...
View Full Document

Ask a homework question - tutors are online