Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Anybody good with Excel?

  1. Mar 7, 2007 #1
    I have a graph of values, and I am trying to add an exponential trendline, but it won't allow me. Can anybody help?
  2. jcsd
  3. Mar 7, 2007 #2


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    So what happens when you try to add the trendline?

    I assume you are clicking on the ploted data series then selecting the chart menu and then add trendline
  4. Mar 7, 2007 #3
    It shows a blank box where the exponential picture is, and the word "exponential" is grayed out.

    EDIT: exponential.jpg
    Last edited: Mar 7, 2007
  5. Mar 7, 2007 #4


    User Avatar

    Staff: Mentor

    That feature may not be installed or enabled yet. Look for Tools, Add-Ins, or something like that. Some of the more specialized tools are not installed with the default installer, but can be added in later as needed.
  6. Mar 7, 2007 #5
    After Add-ins, it still doesn't work.
  7. Mar 7, 2007 #6


    User Avatar

    Staff: Mentor

    What does that mean? Was it listed as an add-in, and after adding it in, the feature is greyed or plain doesn't work? Or was it not listed as an available add-in?
  8. Mar 7, 2007 #7
    There was no add-in for the graphs. I installed all add-ins just in case, but like I thought, nothing.
  9. Mar 7, 2007 #8


    User Avatar

    Staff: Mentor

    Is it a full version of Excel? Or just the sample version that comes pre-installed on new PCs? I think you may need the full version (that you have to buy at the store and install yourself) for that feature?

    You could try the knowledge database at Microsoft -- it's pretty good at helping you to figure out weird ones like this.


    Let us know if you figure it out!
  10. Mar 7, 2007 #9


    User Avatar
    Science Advisor

    Just for kicks, did you try loading in the analysis toolpack add on? I have never seen a trendline option grey'd out like you are mentioning.

    What does the data look like when it is plotted? What kind of plot are you trying to put the trendline in? I would first suggest you make sure that the plot is in an "xy scatter" and not a generic "line" format.
  11. Mar 7, 2007 #10
    I don't know what the "analysis toolpack" is.

    It is an XY Scatter that is basically made to exponential.

  12. Mar 7, 2007 #11


    User Avatar
    Science Advisor

    The analysis toolpack is one of the included add-ins that come with Excel. In my add-ins dialog box, it's at the top of the list when you bring up the add-ins available dialog box.

    If it's not too large, try attaching the file so we can try manipulating it ourselves. That will tell us if it is your version of Excel that is the issue.

    I just pulled this up from MS's tech page that Berkeman referenced:
    Code (Text):
    In addition, the Analysis ToolPak add-in provides a special set of
    analysis tools, including tools to accomplish the following tasks.

                                                Use this
       To do this                               analysis tool

       Predict a value based on the forecast    Exponential
       for the prior period, adjusted for the   Smoothing
       error in that prior forecast

       Project values in the forecast period    Moving Average
       based on the average value of the
       variable over a specific number of
       preceding periods

       Perform linear regression analysis and   Regression
       return statistics and plots as specified

                   Table 2--Analysis ToolPak Add-in Features

    To Use the Analysis ToolPak

    In Microsoft Excel 5.0 and later:

    1. On the Tools menu, click Data Analysis.

    2. If the Data Analysis command is not available, click Add-Ins on the
       Tools menu. In the Add-Ins dialog box, click to select the Analysis
       ToolPak check box.

    NOTE: If the Analysis ToolPak add-in is not listed, run the Setup program,
    choose Add/Remove, and select the Add-ins option for Microsoft Excel.

    In Microsoft Excel 4.0:

    1. On the Options menu, click Analysis Tools.

    2. If the Analysis Tools command is not available, click Add-In on the
       Options menu. In the Add-Ins dialog box, click Add. Click Analysis.xla
       in the Library\Analysis folder.

    3. In the Data Analysis dialog box, choose the tool that you want to use,
       such as Exponential Smoothing. For help on how to use a particular
       analysis tool, click Help in the dialog box for the tool.
    http://support.microsoft.com/kb/103839/en-us [Broken]
    Last edited by a moderator: May 2, 2017
  13. Mar 7, 2007 #12


    User Avatar

    Staff: Mentor

    Aside hijack....

    Hey Fred -- what is your new avatar? It looks like a mil patch. I have two new mil patches on my office door from my FTO colonel Steve from SJPD who re-deployed for a year with his Special Forces unit a few months ago. He was our FTO for my latest work ERT training evolution -- great and challenging stuff. Godspeed colonel Steve. Talk at you when you get back.
  14. Mar 8, 2007 #13


    User Avatar
    Science Advisor
    Homework Helper

    Your last data point seems to have Y = 0. For exponential data fitting all Y values have to be > 0 (because the maths involves taking logs of the Y values)

    I tried a test with Excel, with and without a Y=0 point in the data set. If you remove the point you should get the exponential option in the menu.
  15. Mar 8, 2007 #14


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Sure enough, if your chart has a zero value the exponential fit box is empty!

    Good catch AlphaZero!
  16. Mar 8, 2007 #15


    User Avatar
    Science Advisor

    Hey Berkeman....It is the unit patch of my unit I was stationed with in Germany back in the late '80s and early '90s. 205th Avn Co. "Geronimos." We were definitely not special forces, but we did do some cool stuff. A lot of fond memories to be certain.

    Good catch Aleph! It's always the easy stuff that jumps up and gets ya.
  17. Mar 8, 2007 #16
    Yeah, I figured that out, but thanks anyway.
  18. Mar 8, 2007 #17
    Excellent troubleshooting, Aleph_Zero you should be a consultant:smile:

    Despite the mathematical unpleasantry of Log(0), there is really know excuse for the programmers at microsoft not to do some better error handling on this issue.

    Here is what happens in Mathematica:
    Code (Text):

    expList = Table[{x, Exp[-x]}, {x, .1, 2.1, .1}];  

    FindFit[expList, A Exp[- c x], {c, A}, x]
    Returns the correct result:

    Code (Text):
    {c -> 1., A -> 1.}
    Then if we insert a zero randomly:

    Code (Text):
    expList = Insert[expList, {Random[Real, {0.1, 2.1}], 0} , Random[Integer, {1, 20}]];

    FindFit[expList, A Exp[- c x], {c, A}, x]
    We get the reasonable result:
    Code (Text):

    {c -> 1.07562, A -> 1.01937}
    Which is no more perturbed then would be expList with the addition of a random non-zero point in a similar range.

    MS Excel cost the same amount for students as does Mathematica! Just because Excel is "easy", why is it so bad? Aren't the people at MS smart like the people at WR?
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook