Mr. P: Good morning,
in my opinion physics students have to know how to use a spreadsheet program to analyze their data.
That's why I call it harnessing the power of the spreadsheet.
Billy: Yeah!
Bobby: Okay
Bo: That's weird
Flipping Physics
Mr. P: Am going to demonstrate by using the 2016 version of Excel
However, there are all sorts of other spreadsheet programs like Numbers, Sheets, NeoOffice.
They all have similar functionalities
so this demonstration should be useful no matter what program you use.
Let's start with a basic experiment
Let's say we have a cart on an incline the initial velocity of the cart is zero
We are adjusting the displacement of the cart
and measuring the time it takes for the cart to travel the displacement
We will eventually graph displacement as a function of change in time
Let's start by inputting the information into our spreadsheet
These rectangular boxes are called “cells” and each cell is labeled with a letter in the x-direction
which indicates the name of the column and a number in the y-direction which indicates the
name of the row.
Which means this first cell is "A1".
In A1 let's put "Change in Time (s)"
"Change in Time" labels the data in this column as the change in time and the parenthetical s
tells us that the units for the change in time are seconds.
Notice that change in time is actually too wide for this column and if we double-click between the A and
B column labels you can see that the column width is auto fit to the width of the largest
text in that column.
Okay, now we can input all of the data we collected for the change in time.
(Entering Data)
So that data looks strange aligned to the left of each cell so I can click in the upper left-hand corner
to select all the cells in the worksheet and then
Click on this icon right here to Center align all the text in the worksheet.
Okay, so be careful to notice the tabs at the top here because the tabs change which icons are available.
So the text alignment buttons are under the "Home" tab.
Bobby: Mr. P? 
Mr. P: Yes, Bobby?
Bobby: We could use "delta t" instead of "change in time," yeah?
Mr. P: Sure. We could use delta t. Delta is a Greek symbol
so we would need to go to the insert in the menu icons up here and then symbol.
We can then search for the Greek letter Delta and you can see when we double click on Delta
It adds Delta to our cell. But of course, we don't want it up there.
Let's put it up here instead of change in time. So we could put in
Delta t instead of change in time and that is the same thing.
We can either have "change in time" or "delta t"
because they're same thing. And again we can double click
between a and B to auto fit this column to that column width.
Okay. So next let's add a column with our displacements in centimeters "displacement (cm)".
And again, you can see we need to do an auto fit to fit that column.
Our first displacement was 10 centimeters and
and every displacement there after the initial trial is 10 centimeters larger.
So there's an easy way to input that data and require it requires
we use an equation via the equal sign.
So in cell B3, which is directly below cell B2, we type an equal sign,
and then click on cell B2. Notice how this places B2 into our equation.
Then we can type plus 10 to add 10 centimeters to the original 10 centimeters in B2.
Bo: That seems kind of dumb. We could have just typed in 20 instead of equals B2 plus 10.
Typing 20 would have taken less time.
Mr. P: Sure Bo.
However,  realize we can now select cell B3 and bring the pointer or cursor to the down to the
lower right-hand corner of that cell. And notice how the cursor icon
changes. Which indicates we can now click and drag all the way down to drag and fill the repeated equation.
Clearly this takes less time than typing in all the data, right?
Bo: I guess so.
Mr. P: "Drag and fill" fill copies and pastes the equation and it uses a relative reference.
Which means the equation always uses the cell directly above the cell with the equation in it.
For example, this one uses B3, this one uses B4, can see right up up here it uses B4,
B5,
B6, so on and so forth.
Okay, so there's a lot we could calculate from here.
Let's start with the average velocity in centimeters per second.
So average velocity in centimeters per second.
And again, we need to autofit this column to make it fit the word "average velocity".
Billy, How do you think we could calculate the average velocity?
Billy: Well, I bet we could use the equal sign to do an equation.
The equation for average velocity is displacement over change in time,
so in cell C2 type
"equals B2" and then "divided by" – which is the the forward slash – and then A2.
Mr. P: That is correct Billy.
Notice you can either
type in the cell name A2,
or you can click on the cell using the cursor. And again we can select the cell we want to use to "drag and fill"
Bring the cursor to the lower right hand corner and click and drag down to
"Drag and fill" in the equation.
Bo: Rather than typing "Average Velocity" for the heading of this column of data,
could we use v with a subscript of "avg" for average velocity?
Mr. P: Sure, okay. So the way you would do that is first we need to get rid of
average velocity. So we type in just v and then "avg".
So we need to select just "avg" and then we're going to change just this format of that text to subscript.
So we've selected "avg" and we go to format,
and then cells, and then click on the radio button for subscript and you can see that it changes the
text that we highlighted to the format of subscript.
Billy:  Mr. P. 
Mr. P: Yes, Billy
Billy: Why do we need to put the parenthetical "s" for seconds in the column heading?
Could we not just put "s" next to all of our numbers?
Ah, yes, okay. So notice what happens when I add "s" to these various cells.
You can see we're running into an error with the calculation for the average velocity.
That is because by adding the "s" to the cell, the spreadsheet programs assume that these cells contain
text and not numbers and you cannot do these equations with text.
So, that is why you can't add the units in the cells themselves.
Mr. P: Okay, so does anybody see anything wrong with our data currently?
Bo: Nope
Bobby: Significant figures there's no way we know the average velocity to - that's like 10 sig figs?
Correct, we need to change the number of sig figs we are displaying.
So let's start by selecting the data we want to change the number of sig figs we are displaying.
And then we're going to click on these two buttons right here,
which are located under the Home tab. And you can see that they changed the number of sig figs
which are displayed. I'm going to argue that we know displacement to the
millimeter and we know average velocity to roughly, let's say, three significant figures.
Bobby: Mr. P? 
Mr. P: Yes, Bobby?
Bobby: Why do you keep saying "display"?
It looks to me like we are changing the number not just the display of the number.
Mr.P: Okay, yeah, so it's important to realize we are not changing the number itself,
but rather just changing the number which is displayed by the program.
The spreadsheet does not round the number to what is displayed. It just changes what you see.
Okay. Now let's make a graph or chart.
Click on the "Insert" tab, because we are about to insert a chart.
To the right of where it says "recommended charts", you can see there are a whole bunch of chart options.
Typically in physics we use an "XY scatter" chart.
So we're going to select that and
typically, we're gonna use a scatter chart which does not have lines between the data points.
So let's choose that one.
At this point the program tried to figure out what data we wanted.
However, it did not guess correctly.
We'll deal with that in a moment
However, let's first decide where 
we want the chart to be.
We can click and drag the chart over to the right
where it's it does not cover any of our data.
Or we can move the chart to a different sheet.
To move the chart to
a different sheet, select the chart and then, with "chart design" selected,
you can see there is a "Move Chart" button.
If we click on the "Move Chart" button,
then we can move the chart to its own sheet rather than keeping it as an object in our original sheet.
Notice we now have a different sheet with just the chart on it.
We have our original data and we have the sheet with just the chart on it.
This can be helpful if you have a lot of data or if you want to print the chart large on its own sheet of paper.
In this particular case, we don't have that much data, so I'm actually going to move it back
to be embedded in our original sheet.
And move it so it is no longer covering our data.
Ok. Now we need to select our source data for the chart.
With the chart selected again select the "chart design" tab
and click the "select data" button.
You can see the program decided to add two data series: "Displacement" and "velocity average".
I find the easiest thing to do here is actually to remove all the data so we can start anew.
So click the "minus" button to remove the two data series.
Now click the "plus" button to add a data series to the chart.
If you have multiple data series, it can be helpful to give the series a name.
However, because we only have one data series for this chart, I'm not going to name it.
Now we need to decide what what is going to go on our y- and x-axes.
We're going to graph displacement as a function of change in time.
Can anyone tell me what we need to put then on our y and x axes?
Bobby: Think it as always Y as a function of X, right?
Billy: Yeah, it's always y is a function of x.
So if we are graphing displacement as a function of change in time ...
Bo: Then displacement goes on the y axis and change in time goes on the x axis.
Mr. P: Right. So for the x values click on the button to the right of where it says "x values".
This collapses the dialog boxes so you can more easily access the data.
Click and drag to select the change in time data.
Now you need to select the data only do not include the change in time header.
In my experience including the header sometimes confuses the spreadsheet program.
Again, just select the data.
Once you have selected just the data
press "return" to open the "select data source" dialogue box again and repeat the process for the y-axis values.
Click on the button to the right of the Y values,
select just the displacement data for the y axis and
click "return" to return back to the selected data source dialog box.
And then click "OK" to finish selecting your data.
And there is our data in chart form.
Bobby: I feel like the graph is missing some things.
Bo: Yeah.
Billy: And it looks like one of those data points is wrong and the data point at like 90 centimeters,
does not seem to follow the the general trend of the rest of the data.
Mr. P: Okay. Sure. Let's start with that errant data point.
It looks like that data point is where the displacement is 90 centimeters
and the change in time is 1.038 seconds.
So, let's go back to my original data my original handwritten data, and oh!
Will look at that!
The change in time is actually not 1.038 seconds, It's 1.308 seconds.
So let's fix that. 1.308 seconds and oh, look it fixed the chart and the average velocity.
Oh, will you look at that?
It looks like if you make a mistake, and then you fix it,
that change actually propagates through the entire worksheet.
That seems pretty helpful.
Bobby: I bet he did that on purpose.
Bo: I bet he's going to say that harness the power thing now.
That, my friends, is how you harness the power of a spreadsheet!
Billy: Yeah, harness the power!
Bobby: What about the average velocity?
Why did we calculate average velocity if we're not going to put it in our graph?
Right, we calculated average velocity to further review
using the equal sign to create equations in a spreadsheet.
That's really the only reason we calculated that.
Okay, so now back to the fact that our chart is missing some things.
With the chart selected and with the chart design tab selected,
we can go to the upper left-hand corner to "Add Chart Elements".
And you can see there are a whole bunch of things we can add here.
Let's start with axes titles.
Let's add a "primary horizontal" axis title.
And let's add a "primary vertical axis title".
And you can see that axis title is probably not the best title for our axes.
So let's change them so that they include both the label and the units that are on the axes.
On our vertical axis, for example, we have 
displacement in centimeters.
And on our horizontal axis we have change in time in seconds.
Going back to "add chart element," I don't think our chart title is doing us a whole lot of good this time,
so I'm going to remove that.
And I also don't think that the legend is adding anything to our graph either.
So I'm also going to remove that.
Okay. Now let's add a best-fit line or best fit curve to our graph.
In our program it is called a "trend line." So let's add a trend line.
Again that's under "add chart elements," "trendline".
And I find that using the more trendline options is
actually the best. This presents you with more options to choose from.
It defaults to a linear trendline and I would suggest clicking the radio button,
selecting the radio button "display equation on chart".
This displays the equation for the best fit line or trendline.
In addition, if applicable, you can select the radio button to set the
Y-intercept equal to zero.
Okay now I'm going to move the equation
so it's easier to read.
Okay.
Notice the original best fit line equation is in the
slope-intercept form of a line, y equals m x plus b,
or the y value equals the slope times the x value plus the y-intercept.
Selecting the "set y intercept equal to zero" radio button forces b,
or the y-intercept, to equal zero.
Please be careful with this option to set the y intercept equal to 0,
because trends which should start at the origin but actually don't
indicate details about experimental error.
Okay, clicking back on the trendline brings back the format trendline options.
Billy: Mr. P?
Mr. P: Yes, Billy?
Billy: That trendline does not approximate the data very well.
Bo: And the trendline does not go through the origin
even though you said we set the y-intercept equal to zero.
Mr. P: Okay, let's let's deal with Bo's observation first.
Realize the trendline does not by default
extrapolate beyond the data.
It does not go beyond the data, it only interpolates within the data.
In other words, the trendline defaults to only displaying within the horizontal axis bounds of the known data.
That is why the trendline does not extend to the left beyond our data all the way to the origin.
With regards to the trendline not approximating our data,
that is because when the acceleration of an object is constant
the relationship between displacement and change in time is actually not linear, it is a polynomial of order 2.
Notice when we change our trendline option to
polynomial of order "2,"  the trendline approximates our data extremely well.
Billy: Wow, it really does, that's cool!
Bobby: Yeah.       Bo: Sure
Okay, I want to talk just a little bit more about the physics of this best fit line or trend line.
The spreadsheet program displays the trendline equation with dummy Y and X variables
because it does not know what is on our y and x axes.
However, we know "displacement" is on the y axis and "change in time" is on the x axis.
Therefore this best fit line equation is actually displacement equals, with three sig figs,
50.4 times change in time squared plus 2.48, times change in time.
This equation represents the relationship between displacement
and change in time for our particular experiment.
Billy: Oh boy
So we can use that equation to determine the displacement of the cart for any change in time.
All we have to do is plug the change in time into the equation and bam! We have the answer.
That is awesome!
Bo: That actually is pretty cool. 
Mr.P: Yes, it is pretty cool because it's physics.
And, this trendline actually represents one of our uniformly accelerated motion equations,
displacement equals velocity initial times change in time
plus 1/2 times acceleration times change in x squared.
Bobby: Oh, hold on. Wait a second
I thought the initial velocity was zero in the experiment.
That equation shows the initial velocity to be 2.48 centimeters per second, why is that?
Mr. P: Very nice Bobby, that discrepancy shows that there must be experimental error,
which of course there always is.
Okay, so if you think this is harnessing the power of a spreadsheet,
imagine if you are doing something like this!
Billy: Oh wow, look at that will you?
Bo: That is a lot of harnessing
Mr. P: Yeah, that is harnessing the power of a spreadsheet.
Hehehe
Billy: Can we do that?
Mr.P: Okay, so a few other items which will be helpful when using spreadsheet programs.
For example, you need to be able to multiply stuff.
So two asterisks ten is how you do multiplication. So asterisks is multiplication.
So clearly, equals two asterisk ten is going to give you 20 because that's two times ten.
If you want to do an exponent, you could do two caret (^)
No, let's do 10 caret (^) 2 to that is going to give you
100 because
10 squared is 100.
You could also do 100 raised to the power of 1/2
and that will give you a square root. Right? 
100 raised to the power of 1/2.
Hopefully you recognize is going to be equal to 10
So that symbol – just so you know that simple it looks like this –
is the caret symbol and that's not to be confused with a carrot, which is a vegetable, or
carat, the mass we use to identify the mass of diamonds, for example.
Okay, let's keep moving.
So pi, you're gonna have to use pi at some point.
In order to get pi, you need to use the four letters P I and then start parenthesis and parenthesis.
So, for example, if we want to do two times pi,
what we need to do, is that's going to be equal to two times pi start parentheses and parentheses.
And that will give us two times pi.
Be careful to use parentheses when necessary.
For example, 4 divided by 2 times 2. You should be able to tell me what 4 divided by 2 times 2 is equal to.
It's equal to 4.
And you should be able to tell me with what 4 divided by the quantity 2 times 2 is equal to.
Again, 4 divided by the quantity 2 times 2, which is not the same.
It's 1.
Please be careful of your order of operations. Pay attention to when you need to use parentheses.
And the last thing is printing.
Notice what happens when I go to "file," "print".
it defaults to a portrait orientation and with not printing gridlines or borders in your data table.
So those are things we need to fix.
So we're going to cancel this and we will start by adding borders to our data table.
So if we select all the data in our data table,
we can go to "format cells" and then in the border tab, we can click both "outside" and "inside".
And click "OK' and you can see we now have borders on all of our data table.
Which makes it much easier to read the data table.
And we can also go to the "file" and "Print page setup"
and we can change it to a "landscape orientation" and "fit" to "one-to-one pages" wide  and tall.
And if we click "OK" and now go to file and print, you can see that it now fits all on
one page and you can see the data table itself
has borders on it, which makes it much easier to read.
So every time you go to print,
please make sure that you check to see if you should be in portrait or landscape orientation,
adjust the scaling to make it fit how it should, and
add borders to your data table to make it easier to read your data table, please.
Ok, I'm sorry however, there is one more thing we need to discuss.
I showed only one way to do everything we did in this video,
however, there are often many ways to get to the various dialog boxes you need. For example,
if you right-click or two finger tap on the center of the chart
it brings up a contextual menu where you can choose
"Select data" to bring up the Select data source dialog box.
You can right click or two finger tap on the trendline itself to bring up the "format trendline" dialogue box.
If you right click or two finger tap on a cell you can
click on "format cells" to bring up the format cells dialog box.
I highly recommend you become familiar with the contextual menus that come up
when you right click or to finger tap on items.
Contextual menus are menus of options that change depending on the context of what is being selected.
Contextual menus can be very helpful.
Also notice the symbol to the right of the format cells in the contextual menu box.
Notice this shows you that the keyboard shortcut for format cells is command-1.
If you have a cell selected and press both command and 1 keys at the same time,
the format cells dialog box box pops up.
Keyboard shortcuts for operations you perform repeatedly can really save time.
Okay, I'm finally done. I'm sorry this took so long, but I really had a lot to say about
harnessing the power of spreadsheets in physics.
Thank you very much for learning with me today. 
I enjoyed learning with you.
