Basic curve fitting with Excel
1. DATA
There must be two columns of data of the same size. Column one will be the X axis (independent, input, predictor data), and column two will be the Y axis (Dependent, output, response data).
2. SCATTER PLOT
Highlight the two columns. Select Insert (top of screen) and then select Scatter for your chart. To label the axis, click on the scatter plot (it doesn't matter where). You should automatically be in the Design tab. In the top left corner, select Quick Layout, and choose the top left most option (Layout 1). This will add generic labels to the chart. Right click on the labels to edit them.
3. FITTING A MODEL
Highlight your chart is highlighted (click on the chart anywhere). Three icons will appear on the right, click on the Plus icon and select Trendline. The arrow over next to the Plus icon provides more trendline options such as exponential, polynomial, moving average, linear, and logarithmic. When selecting polynomial you need to set the order of the polynomial (2 is quadratic, 3 is cubic). At the bottom of the Format Trendline box choose Display Equation and Display R squared. You can choose to give the equation a name, choose to have the equation extrapolated forward or backward, and set a fixed intercept value.
² Changing Axis Range To change the range on a particular axis (say the x-axis) you need to first right click on the axis and select format axis.
² Forecasting You can extend the model (trendline) forward or backward beyond the data set: Right click on the trendline and go to format trendline. This brings up the original dialog box that allows you to adjust the forecast value forward or backwards.
² Setting the decimal places in a model Once a trendline has been added to a graph you may need to add decimal places to the equation, especially if excel starts using scientific notation (significant digits are often lost when excel does this). Highlight the equation that you want to edit, right click and choose format trendline label. Choose Number and then you will be able to set the decimal places. As a rule it seems safe to go one more decimal place than needed to get rid of the scientific notation. Note also that when you highlight and right click the equation you can change the font size.
Curve Fitting with Excel (1997-2003)
(Based from: http://www.ithaca.edu/tpfaff/pdf/CurveFittingWithExcel.pdf)
1. DATA
Data There must be two columns of data. One column of "x" (input, predictor, independent) data, and one column of "y" (output, response, dependent) data. The size of both data sets must be the same.
2. SCATTER PLOT
First highlight the input x and output y data by holding the left mouse button down. If the X and y data columns are not adjacent then highlight the x data while holding down the control key, lift your finger or the left button after the x data is highlighted and then highlight the y data. Once this is done go to Insert Chart. In the dialog box choose scatter plot and follow your way through by clicking next. In step 3 of the cart wizard you should label your axes and give the chart a title. In step 4 you have the option of putting the graph in as a new sheet (you will get a larger graph to view and is often helpful when making more than one graph) or as an object in a worksheet -recommended- (your graph will be small but in the same worksheet as the data set, this is good if you only want a couple of graphs from a given worksheet).
3. FITTING A MODEL
First be sure that your chart is highlighted (left click on the chart anywhere if it isn't). Go to Chart & Add Trendline. You have 6 options. Note that when selecting polynomial you need to set the order of the polynomial (2 is quadratic, 3 is cubic). Before clicking Ok, go to the option tab and check Display Equation and Display R squared. Note also that under options you can choose to give the equation a name and you can choose to have the equation extrapolated forward or backward.
SOME OPTIONS AND DETAILS
² Changing Axis Range To change the range on a particular axis (say the x-axis) you need to first click on the axis so that in the upper left corner it says Value (X) axis. Now go to format & selected Axis or Ctrl 1. In this dialog box click on the scale tab and set the range.
² Forecasting You can extend the model (trendline) forward or backward beyond the data set: Right click on the trendline and go to format trendline. Under the options tab and adjust the forecast value forward or backwards.
² Setting the decimal places in a model Once a trendline has been added to a graph you may need to add decimal places to the equation, especially if excel starts using scientic notation (signicant digits are often lost when excel does this). Right click on the equation you want to change and select Format Data Labels. Choose the Number tab and go under Number in the Category. A box will appear to set the desired number of decimal places. As a rule it seems safe to go one more decimal place than needed to get rid of the scientific notation.
