Video describing the Excel Chart Wizard

Short Instructions:

1. Enter the data into a worksheet as shown in the below screen shot.
2. Click on an empty cell within the worksheet and then click the Chart Wizard button from the toolbar.
3. Choose the XY (Scatter) option.
4. From the next dialog window, you can use the mouse to highlight the x and y-values that will be plotted along the axes.

5. After clicking the Finish button, the graph will appear.
6. After Excel has created the graph, add a trend line. That is, make the computer draw the best-fit line to the data.
7. Since the relationship between the absorbance and the concentration is linear, we can expect the plotted data to form a straight line in the form of y = mx + b. To add the trend line, click anywhere on the graph and then click on Chart >> Add Trend line from the menu bar. Since we expect the fit to be linear, select linear fit.

8. You can also display the equation and the r-squared value on the graph (look around under the trend line options). The r-squared value is actually the square of the correlation coefficient. The correlation coefficient, r, gives us a measure of the reliability of the linear relationship between the x and y values. A value of r = 1 (or -1) indicates an exact linear relationship between x and y. Values of R close to 1 indicate excellent linear reliability. If the correlation coefficient is relatively far away from 1, the predictions based on the linear relationship, y = mx + b, will be less reliable.

It is also possible for Excel to add trend lines other than linear ones. For example, you may choose logarithmic, exponential, polynomial, power series, or a moving average, depending on your knowledge of the relationship between the data. The best way to learn Excel is to play around with it, looking at all the menu choices carefully.

Nonlinear relationships, in general, are any relationships which are not linear (Tautology?). What is important in considering nonlinear relationships is that a wider range of possible dependencies is allowed, for instance as x changed y could change as the square (or square root) of x . When there is very little information to determine what the relationship is, assuming a linear relationship is simplest and thus, by Occam's razor, is a reasonable starting point. However, additional information can reveal the need to use a nonlinear relationship.

Many of the possible nonlinear relationships are still monotonic. This means that they always increase or decrease but not both. Monotonic changes may be smooth or they may be abrupt. For example, a drug may be ineffective up until a certain threshold and then become effective. Nonlinear relationships can also be non-monotonic. For example, a drug may become progressively more helpful over a certain range, but then may become harmful. Thus the benefit from the drug both increases and decreases and is a non-monotonic, nonlinear, relationship.

Answer some questions (almost done!)