Excel trendline formula: capital E

[ottlots]

I made a chart in Excel. I added a trendline. I printed the trendline equation on the chart. The equation is:

y=5E+16e[to the power of]-3E-04x

The "-3E-04x" are in superscript. I know what the variables x and y mean. I believe e is the math "e" = 2.718 or so.

My question is, what is the capital E?

Also, why is the "04x" listed that way? Shouldn't it be "4x"

Thanks.

 

[joeu2004]

I printed the trendline equation on the chart.
The equation is:
y=5E+16e[to the power of]-3E-04x

The "-3E-04x" are in superscript. I know what the
variables x and y mean. I believe e is the math "e"
= 2.718 or so. My question is, what is the capital E?

You are correct about the lowercase "e". More precisely, it is EXP(1),
where EXP() is an Excel function.

Uppercase "E" is the Scientific notation for "10 to the power of".
So -3E-04x is "x times -3 times 10 to the power of -4", or -0.0003x.

Likewise 5E+16e is "5 times 10 to the power of 16, that times e to the power
of ...".

So if you were to write that in an Excel formula, ostensibly it would be:

=(5E+16)*EXP(-(3E-04)*X1)

I have over-parenthesized for clarity.

However, it would be wrong to copy the trendline coefficients exactly as
they are displayed. There rounded values are usually too inaccurate to use
into an Excel formula.

If you are interested in copying the trendline coefficients into an Excel
formula, let us know, and we can offer proper instructions.

 

[ottlots]

"ottlots" <[email protected]> wrote: > I printed the trendline equation on the chart. > The equation is: > y=5E+16e[to the power of]-3E-04x > > The "-3E-04x" are in superscript. I know what the > variables x and y mean. Ibelieve e is the math "e" > = 2.718 or so. My question is, what is the capital E? You are correct about the lowercase "e". More precisely, it is EXP(1), where EXP() is an Excel function. Uppercase "E" is the Scientific notation for "10 to the power of". So -3E-04x is "x times -3 times 10 to the power of -4", or -0.0003x. Likewise 5E+16e is "5 times 10 to the power of 16, that times e to the power of ...". So if you were to write that in an Excel formula, ostensibly it would be: =(5E+16)*EXP(-(3E-04)*X1) I have over-parenthesized for clarity. However, it would be wrong to copy the trendline coefficients exactly as they are displayed. There rounded values are usually too inaccurate to use into an Excel formula. If you are interested in copying the trendline coefficients into an Excel formula, let us know, and we can offer proper instructions.

Thanks - your explanation adds some clarity around E, but I must have omitted some important details or I am applying this wrong, because the results I am getting don't make sense.

The "x"s in this case are dates at the end of the month: Aug 31, 2012, September 30, 2012, etc. These are in date format in Excel, but are whole numbers in formulas (41152 and 41182 respectively). The "y"s are portfolio balances. The trend has been a more or less straight line decline from 37.7B in Dec 2011 to 34.5B in Aug 2012. Roughly speaking, the result using September 30 end of month (41182)as "x", I would expect this formula to generatea "y" of greater than 33B and less than 34.5B. Plugging the known numbersin to the formula, I am getting 215B. In the chart, the trendline follow the expected trajectory. I am just trying to find the exact number the formula is generating for subsequent months.

 

[joeu2004]

I printed the trendline equation on the chart.
The equation is:
y=5E+16e[to the power of]-3E-04x

I must have omitted some important details or I am
applying this wrong, because the results I am getting
don't make sense.

Most likely the latter ("apply this wrong").

The "x"s in this case are dates at the end of the month:
Aug 31, 2012, September 30, 2012, etc. These are in date
format in Excel, but are whole numbers in formulas
(41152 and 41182 respectively). [....]
Roughly speaking, the result using September 30 end of
month (41182)as "x", I would expect this formula to
generate a "y" of greater than 33B and less than 34.5B.
Plugging the known numbers in to the formula, I am getting
215B.

I agree that the following formula results in about 215E+09:

=5E+16*EXP(-3E-04*A2)

I anticipated the problem. As I wrong before: "it would be wrong to copy
the trendline coefficients exactly as they are displayed. There rounded
values are usually too inaccurate to use into an Excel formula".

The point is: 5E+16 and -3E-04 are rounded approximations. This is for
display purposes only. In fact, you can format the trendline "label"
(displayed formula) so that it displays more precision.

I suggest using Scientific with 14 decimal places if you intend to copy the
coefficients into an Excel formula. That is still inaccurate. But it is
the best we can do, since Excel limits its displayed values to 15
significant digits. And that is probably good enough.

In the chart, the trendline follow the expected trajectory.
I am just trying to find the exact number the formula is
generating for subsequent months.

There is a better way to enter the coefficients into Excel: use LINEST in a
particular way.

However, without the data (X and Y) used to generate the trendline, I cannot
be more specific.

If you want more guidance, you can upload an example Excel file (devoid of
any private data) that demonstrates the problem to a file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here. The following is a list of some free file-sharing
websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

In this case, it should be sufficient to create a new workbook,
copy-and-paste the values for X and Y, then create the chart and the
trendline.

 

[ottlots]

"ottlots" <> wrote previously: > I printed the trendline equation on the chart. > The equation is: > y=5E+16e[to the power of]-3E-04x "ottlots" <>wrote: > I must have omitted some important details or I am > applying this wrong, because the results I am getting > don't make sense. Most likely the latter ("apply this wrong"). "ottlots" <> wrote: > The "x"s in this caseare dates at the end of the month: > Aug 31, 2012, September 30, 2012, etc.. These are in date > format in Excel, but are whole numbers in formulas > (41152 and 41182 respectively). [....] > Roughly speaking, the result usingSeptember 30 end of > month (41182)as "x", I would expect this formula to > generate a "y" of greater than 33B and less than 34.5B. > Plugging the known numbers in to the formula, I am getting > 215B. I agree that the following formula results in about 215E+09: =5E+16*EXP(-3E-04*A2) I anticipatedthe problem. As I wrong before: "it would be wrong to copy the trendline coefficients exactly as they are displayed. There rounded values are usuallytoo inaccurate to use into an Excel formula". The point is: 5E+16 and -3E-04 are rounded approximations. This is for display purposes only. In fact, you can format the trendline "label" (displayed formula) so that it displays more precision. I suggest using Scientific with 14 decimal places if you intend to copy the coefficients into an Excel formula. That is still inaccurate. But it is the best we can do, since Excel limits its displayed valuesto 15 significant digits. And that is probably good enough. "ottlots" <> wrote: > In the chart, the trendline follow the expected trajectory. > I am just trying to find the exact number the formula is > generating for subsequent months. There is a better way to enter the coefficients into Excel: use LINEST in a particular way. However, without the data (X and Y) used to generate the trendline, I cannot be more specific. If you want more guidance, you can upload an example Excel file (devoid of any private data) that demonstrates the problem to a file-sharing website. Then post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here. The following is a list of some free file-sharing websites; or use your own. Box..Net: http://www.box.net/files Windows Live Skydrive: http://skydrive.live.com MediaFire: http://www.mediafire.com FileFactory: http://www.filefactory..com FileSavr: http://www.filesavr.com RapidShare: http://www.rapidshare.com In this case, it should be sufficient to create a new workbook, copy-and-paste the values for X and Y, then create the chart and the trendline.

Thanks - changing the format of the stated formula for the trendline got meclose enought to what I need. After re-reading your original warning, it made sense. Thanks again.