I am going to show you how to make nice plots of x-y data, and we're first going to do a
regression analysis. So I've got percent nitric acid, and we've got heat capacity, so this
essentially is our independent variable, and this is our dependent variable, or the output,
and to do regression in excel, especially if we want to do polynomial I need to move
this over, and here we're going to have our x squared term, and x cubed, and if I wanted
I could do x fourth, fifth, and so on, and now I just put in the formulas for these,
and now I am going to double click on that to auto fill. Now I go over here to data, data
analysis, regression, and for our input y we're going to put that, making sure
my labels block is clicked because I have that upper one, the label here on that first
row. Input x is going to be all these, that is going to be our input, and I am just going
to add a residuals, if you want, a lot of times I like to do residual plot but that's
not really necessary. So I've got my residuals, click OK, it spits out our output, we're going
to highlight those columns and auto-resize by double clicking on a boundary between two
of the columns up above, and now I got my regression output. So these are the coefficients
in our model, so our model looks like a plus bx plus cx squared by dx cubed. These are
the coefficients. The p-value, we want the p-value to be less than point zero five for
all of the terms, and that means that those terms are significant, and all of those are
less than point zero five. If we did have one of these that was greater than point zero
five then that just means that that particular term is not needed in that model. And then
another thing that's spit out, because we clicked that residuals box, is the predicted.
So I am going to just copy that, I am going to bring this back over here and I am going
to past, and I am going to just change the number of decimals that's shown, and one thing
I am going to do just to make it easier to plot, it's a lot easier to plot if you have
x and then y. So I am going to move my x's back over here, and I can highlight this.
If you highlight x-y you can just go up here to insert, chart, scatter, and so we inserted
that scatter diagram, we go over here to the plus sign, and I can add access titles and
I am going to rename this heat capacity, and I am going to rename this percent HNO3. So
now we got that formatted what we want to do is to leave these experimental data points,
so these are the experimental heat capacities, we want to leave those as markers, and we
want to add our model predictions. This is our model predicted. These I can copy, so
I highlight those, Control C, and if I just click once on the inside of that plot and
I do Control Paste it adds that second set, assuming we have the same independent variables
on the x-axis, which we do. So now I click on the pink dots, the pink dots represent
the our model predictions, but we want to make model predictions as a smooth line, so
I am going to right click on that, and format data series. It brings up the options over
here, you click on the paint bucked, and I go to solid line. I am just going to change
that to, let's say red, and then at the bottom of this box is a smooth line, so that's the preferred
way to add a smooth line, and then I go here to marker, and I am going to eliminate my
markers by changing from automatic to none, and I am going to x that box out, and now
we have our model predictions, which is the red line, and we have our experimental data
points which are the blue dots, and that is how you make a nice plot of predicted versus
actual for a regression analysis. Thanks for watching.