# Anybody good with Excel?

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
Staff Emeritus
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

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:

Last edited:
berkeman
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.

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

berkeman
Mentor
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?

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

berkeman
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.

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.

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:
Code:
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

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,

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
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:
berkeman
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.

AlephZero
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.

Integral
Staff Emeritus
Gold Member
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.

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.

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:
Code:
expList = Table[{x, Exp[-x]}, {x, .1, 2.1, .1}];

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

Returns the correct result:

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

Then if we insert a zero randomly:

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