Why won't Excel allow me to add an exponential trendline?

[Plasma]

I have a graph of values, and I am trying to add an exponential trendline, but it won't allow me. Can anybody help?

 

[Integral]

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

 

[Plasma]

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

It shows a blank box where the exponential picture is, and the word "exponential" is grayed out.

EDIT:

 

[berkeman]

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.

 

[Plasma]

After Add-ins, it still doesn't work.

 

[berkeman]

After Add-ins, it still doesn't work.

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?

 

[Plasma]

There was no add-in for the graphs. I installed all add-ins just in case, but like I thought, nothing.

 

[berkeman]

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.

http://support.microsoft.com/search/

Let us know if you figure it out!

 

[FredGarvin]

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.

 

[Plasma]

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.

I don't know what the "analysis toolpack" is.

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

 

[FredGarvin]

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:

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

 

[berkeman]

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.

 

[AlephZero]

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.

 

[Integral]

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

Good catch AlphaZero!

 

[FredGarvin]

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.

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.

 

[Plasma]

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.

Yeah, I figured that out, but thanks anyway.

 

[Crosson]

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)

Excellent troubleshooting, Aleph_Zero you should be a consultant

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:

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

FindFit[expList, A Exp[- c x], {c, A}, x]

Returns the correct result:

{c -> 1., A -> 1.}

Then if we insert a zero randomly:

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:

{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?