|
|
Plotting Tutorial with LibreOffice Calc (or
Microsoft Excel) NS101 FALL 2014 |
|
LibreOffice Calc is
a powerful spreadsheet program, with which you can display data,
perform
calculations and create plots. The aim of this tutorial is to
introduce
and familiarize you with some specific tools that we use in
NS101.
Follow these steps one by one to learn the basic operation of the
spreadsheet
software. Since LibreOffice
Calc
is very similar to Microsoft
Excel,
you can almost directly apply these tutorial instructions to Excel also (you will
see notes for Excel
users in some steps where Excel
differs from Calc,
and the
notes are indicated by 
).
Tutorial Menu
1. Start the Program
To launch the program, go
to Start
on your windows desktop, and then go to Programs >>
LibreOffice (version) >> LibreOffice
Calc
For Microsoft Excel,
go to Start >>Programs >>Microsoft Office >>Microsoft
Office Excel
When you open Calc, it
will show an
empty spreadsheet, named “Untitled 1”. Each
spreadsheet is called worksheet
and you are now on “Sheet 1”
as you can see at the bottom of the sheet.
Each
rectangle is called cell,
and
each cell has a unique name defined by column and row number.
Columns are identified
by letters (A, B, C, …) in horizontal bar at the top of the
worksheet, and rows
are identified by numbers (1, 2, 3, …) in vertical bar on the
left side of the
worksheet. For example, the first cell on the left upper corner
has a name
A1.
When you single click a
cell, you can
select the cell; whereas double click makes you edit
the cell. Let’s double click the cell A1. You now see
the blinking
cursor inside the cell, and whatever you type there will appear also in
the formula bar
on top.
You
can type
any text in a cell, or you can put a mathematical formula and have Calc
perform the mathematical operation. Calc accepts
following
operators:
|
Addition |
+ |
|
Subtraction |
- |
|
Multiplication |
* |
|
Division |
/ |
|
Power |
^ |
Mathematical formula
must begin
with “=”:
For
example,
to calculate 4 × 6, type “=4*6” in a cell and hit the
enter key. The
calculation result appears in the cell, but the formula bar shows the
formula
you entered. Notice that if you don’t have “=”
in front of the formula,
it will just show “4*6” in the cell, as text.
Calc also has some defined
mathematical
functions, such as square-root, 
, and exponent (e),
as shown
below.
|
Function |
Example |
Formula |
Result |
|
Square root |
|
SQRT(2) |
1.41 |
|
|
 |
3*PI() |
9.42 |
|
Exponent |
e−4 |
EXP(-4) |
0.02 |
|
Sine |
sin( |
SIN(PI()/2) |
1 |
|
Cosine |
cos( |
COS(3*PI()) |
-1 |
Using these pre-defined
functions,
let us calculate an area (
) of a circle with a given
radius. Enter the 5 different radius values as shown in the left
figure. To calculate the area for the first radius (0.1 m),
double click
on B3 cell and enter the formula: “=PI()*”, and click
on the A3 cell,
where the radius value is. This automatically puts
“A3” in your formula
and highlights the A3 cell, and takes the value of the cell. Now,
continue typing the formula by adding “^2”. When you
hit the enter key,
the result shows as “0.03”.
To
go out of
the input mode without modifying the formula, just hit the escape (ESC)
key.
Now, if you want to do the
same
calculation for all other radius values, you can easily copy-paste the
formula
as follows:
Click
on the
B3 cell, and click and hold the black square (■) on the lower right
corner.
While still holding the mouse, drag the cursor (shown now as +)
straight down
the column B to B7, and let go. All the cells (B3 to B7) should
be filled
with the results.
Click on one of the cells, B5, for
example – notice that the formula is modified to “=PI()*A5^2”, to take into account the radius of
0.3 m in the
cell A5.
Two more useful mathematical functions
are Summation (SUM) and Average (AVERAGE). Both functions
take a range
of numbers/cells as an input, instead of a single number/cell.
SUM
Adds all the numbers in a range of cells.
Syntax
SUM(Number1; Number2; ...; Number30)
Number
1 to Number 30 are up to 30 arguments whose sum is to be calculated.
Example
SUM(1;2;3;4;5) will return 15 as the result.
SUM(A1;A3;B5)
calculates the sum of the three cells.
SUM
(A1:E10) calculates the sum of all cells in the A1 to E10 cell range.
AVERAGE
Returns the average of the arguments.
Syntax
AVERAGE(Number1; Number2; ...Number30)
Number1; Number2;...Number 0 are numerical
values or
ranges.
Example
AVERAGE (1;2;3;4;5) will return 3 as the
result.
AVERAGE (A1;A3;B5) calculates the average of
the three
cells.
AVERAGE (A1:E10) calculates the average of all cells in the A1 to E10 cell range.
More
detailed complete list of functions can be found under the Help >> OpenOffice.org Help:
search for “mathematical functions”.
In Excel,
the list of functions is found in Formulas
tab, under Math & Trig.
4.
Graphing
Let us go back to the radius and area data,
and create
a Radius vs. Area graph. The radius is now extended up to 1.0
m. To make a graph, you highlight the data cells (both x and y) that you want to plot the graph
with. The left column is to be plotted on x axis, and the right column is on y axis. In this case, the
range of the cells is A3:B7, and Radius (column A) will be on x axis, Area (column B) will be on y axis.
Once the
data range is highlighted, click on the “Chart” icon
on top 
(or
alternatively, go to Insert >>
Chart)
It plots a
default Column chart, and a Chart Wizard window (see below)
opens.
In the window, select “XY (Scatter)”.
In Excel,
inserting a chart can be done from Insert
tab, and then choose Scatter
>> Scatter with only Markers.
The graph now has been changed to XY scatter
plot
(without lines).
Notice that
the radius is in x-axis and area is in y-axis. You can choose to
finalize
the graph by clicking “Finish” or choose to modify it by
going through Data
Range, Data Series, and Chart Elements by clicking
“Next
>>”.
In Chart
Elements, you can put Title and axis labels of the graph. You
can
also choose to display or not display the legend (shown on the right in
this
graph that says “Column B”).
To insert
elements or format the chart, you can double click the chart so that
the gray
border will appear. Insert and Format menus on the
toolbar
at the very top are now customized for the chart.
In Excel,
Chart Tools tab appears when you
click on the graph area, and all of the above-mentioned tools to modify
your
graph are found under the Chart Tools.
For example,
To change data range, data series:
Design >> Select Data
To add a char title: Layout
>> Chart Titles
To add axis labels: Layout
>> Axis Titles
To edit legend: Layout
>> Legend
The final
graph may look like this:
This
graph is not linear (i.e., not straight line). As we know, area
of a
circle is , so area A is not directly
proportional to r,
and the Area vs. Radius graph becomes a curve (a graph of y = ax2).
This is fine, but for science experiments we usually need to
“fit” a line to
the data points, to describe the general trend suggested by the
data.
This general trend line will then tell us the relation between the
variables
plotted. It is much easier to fit a straight line to the data
than a
curve, so it is preferred that you plot data in a way you would expect
a linear
relationship.
What
variables do you have to plot in order to obtain a straight line graph
in this
example? What is A directly proportional to?
The
answer is that A is directly proportional to r2,
and therefore, Area vs. Radius2 graph should give
you a
straight line.
When
a graph is printed on a sheet of paper, the graph should cover at least
80%
of the sheet area for
clear presentation.
So before printing it out, do the
following:
Change the page format to Landscape: Go to Format
>> Page… , and in the
window go to Page. Click on Landscape
orientation.
Also make sure you have the Format set to A4, not Letter.
Click on the chart that you
want to print out, and copy it and paste it to another empty worksheet
(Sheet
2, for example).
Preview the page: Go to File >> Page Preview. Adjust
the size of the graph and the page
margins as needed, using the tool bar on top (see the below image).
To change margins, click on Margins.
To adjust the size of the
graph, use the slide bar
In Excel,
printing a graph can be simpler:
Click on the graph so that
the graph is selected, then go to the office button 
, and click Print
>> Print Preview. The default
should be landscape, and you should only see the graph maximized to the
printing area of the paper. If you need
to change the margin or other page setup, click on Page
Setup in the preview window (or from the worksheet, click on Page Layout tab). Make sure
your paper size is set to A4.
Here is
an example of a printout. Notice that
there are header
and footer
information printed, with your name on it. You MUST put
your name on your graph so that
you can identify your printout when you pick up from a printer!
The default header is “Sheet 1”
and footer is “Page
1”.
To change
header or footer, go to Format Page
on the preview tool bar (see the above image), and go to Header
or Footer
section. There, click on Edit.
You can
type any header / footer information that you want to be appeared on
your graph
print out. The dropdown menu already has
some options with your name in them, so you can also choose one of
those.
Only your
name is absolutely required, but printing date may also be useful.
Footer
information is optional.
In Excel,
the same things can be done from a print preview window:
Go to Page Setup >> Header/Footer
Or alternatively from the worksheet, go to Insert >> Header & Footer
6.
Plotting Exercise
You measure the radii and areas of various circles in order to determine the relation between the radius and its area of a circle. The data collected are shown in the table below.
| Radius | Area |
| (m) | (m2) |
| 0.19 | 0.2 |
| 0.34 | 0.4 |
| 0.41 | 0.5 |
| 0.60 | 0.8 |
| 0.62 | 1.1 |
| 0.68 | 1.5 |
| 0.75 | 2.1 |
| 0.92 | 2.9 |
| 1.03 | 3.1 |
1. Enter these data in a worksheet. If your entered value shows less significant figures (for example, you entered 0.60 but shows as 0.6), you can change that by right clicking on the cell: choose Format Cells... >> Number, and you see an option of changing Decimal places.
If default language of Calc or Microsoft Excel is Turkish, you might get "number stored as text" (or "#VALUE!") error if you type decimal points by using point (.) such as (1.02). In this case you should either use comma (,) to write decimals such as (1,02) or change default language to English by following these steps:
-
Go to Tools >> Options... and choose Language Settings >> Languages.
-
Set Locale setting to English (USA) and check the box for the "Decimal separator key" option that says "Same as Locale setting (.)"
Microsoft
Excel
-
Click Start, click All Programs, click Microsoft Office, click Microsoft Office Tools, and then click Microsoft Office 2007 Language Settings.
- Click the Editing Languages tab.
- Choose your default language as "English (United States)".
2.
Plot the Area vs. Radius2 graph.
To
do this, you will need to first make a column of Radius2 and
calculate them. Then, follow the instruction described in the Graphing section above.
By
default, the first column of your data selection is plotted on x-axis
and the
second column on y-axis. Make sure that
you have Area in y-axis and Radius2 in x-axis. If you
need to
switch them, go to Data Series in the Chart Wizard, and
select
the correct range for X-Values and Y-Values (or alternatively, double
click the
chart, and go to Format >> Data
Ranges… to open the same
window).
Clicking
Select Data Range icon 
will
let you choose the data range interactively.
In Excel,
under the Chart Tools tab, Select Data
>> Edit lets you edit
X Y data ranges separately. Click on 
to
choose the range interactively.
3.
Make sure you put the title and axis labels (with units) on your graph.
4. To "fit" a line or curve using the spreadsheet, do the following:
- Select the chart by double-cilcking the chart
- Right click on a data point and select "Insert Trend Line" ("Add Trendline" in Excel)
- Select Regression Type -- "Exponential", "Linear", etc., and also select "Show Equation" ("Display Equation on chart" in Excel)
5. To "fit" a line to your data by hand, print out the chart (make sure your name is on the header) and follow the step 6.
6. Draw a straight line that fits the data (this is called the best-fit line). The ideal best-fit line should have equal number of data points on each side of the line, with minimum possible distance from each data point. With real experimental data, any of the data points may not be on the best-fit line, but it is fine as long as the line satisfies these conditions and describes the trend of the data.
Examples of a good fit and a bad fit:
7.
Calculate the slope of the best-fit line. Show your calculations
on the
graph paper.
Example: A
graph of distance (d) vs. Time (t) is plotted, and the
best-fit
line has been drawn. For the slope calculation, pick any two
points on
the best-fit line and make a right triangle, as shown in the
figure. Do
NOT use the actual data points unless they are exactly
on the
best-fit
line. A bigger triangle is better. The slope of a
straight
line is 
, so here we have:
Always
keep the units in your
slope calculation: the slope also has units (or can be unitless when x
and y units cancel each other).
Note the units of the
slope here –
this is the velocity.
8. What is this slope supposed to be according to the area-of-a-circle formula? Write down your answer on the graph paper.
NS101 Plotting Tutorial,
Sabancı
University
Author: Yuki Kaneko Last
modified on: 1 April, 2014