Changing Digits in Trend Line Equation

Significant Digits in a Trend Line Equation

When Excel plots your trend line and displays its equation, it is almost hit-or-miss on how many digits it will display.  In particular, for large numbers displayed with powers of ten, like 5E-34, it may only show one digit.

If you’ve been doing your experiment correctly, chances are that more than one digit in your fit should be significant.  In older versions of Excel, it was necessary to play with the scale of your x and y axes in order to fix this type of a problem.

For example, if your x-axis was in kilovolts, you might need to plot your data by dividing your x-series by 1000, so that 4000 would be plotted as 4.000.  (This can still be helpful sometimes, but you do need to be careful when using the fit, as it will be off by a factor of 1000.)

In the latest version of Excel, however, you can change the number of digits displayed in your fit by doing the following…

  • Right click on the trend line equation. (Sometimes you might need to click twice.)
  • Click on “Format Trendline Label” from the options that appear.  A new dialog panel will show up to the right.
  • Under Number\Category select “Number” or “Scientific” from the drop down list.  You will be given the option for how many decimal places to display.  Choose the appropriate precision based on your measurements.
The Trendline Format Dialog, accessed by right-clicking on the trendline equation.

The Trendline Format Dialog, accessed by right-clicking on the trendline equation.

Why would you want to change the number of digits displayed?

Often we use the fit of the trendline to determine a particular quantity.  In the example shown, we are using the fit to determine the acceleration of an object sliding down an incline.  In the extreme case where only one digit is displayed in our first term, our actual acceleration has a wide range of possibilities.

In the image shown, if we only had one digit in our trendline, the first term would be displayed as 3x2.  The original number could then have ranged from 2.51 to 3.49.  Since our acceleration is 2 times this number, the result would range from 5.02 m/s2 to 6.89 m/s2.  This range is much too large for the equipment used in this experiment.

Using the more appropriate 2.75 shown, our result lies in the range of 5.49 m/s2 to 5.51 m/s2, a much smaller range.  Remember, the precision of our measuring instruments determine the precision of our final calculated result.

If you have questions or comments, please use the form below.