Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Excel trendline formula: capital E
33,470 views
Skip to first unread message
ottlots
Oct 23, 2012, 3:26:07 PM10/23/12
to
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.
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
Oct 23, 2012, 6:17:47 PM10/23/12
to
"ottlots" <ott...@gmail.com> 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. 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),
> 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?
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
Oct 26, 2012, 5:45:00 PM10/26/12
to
On Tuesday, October 23, 2012 5:17:48 PM UTC-5, joeu2004 wrote:
> "ottlots" <ott...@gmail.com> 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. 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.
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.
> "ottlots" <ott...@gmail.com> 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. 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.
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 generate a "y" of greater than 33B and less than 34.5B. Plugging the known numbers in 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
Oct 26, 2012, 6:15:20 PM10/26/12
to
"ottlots" <ott...@gmail.com> wrote previously:
"ottlots" <ott...@gmail.com> wrote:
> 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).
[....]
=5E+16*EXP(-3E-04*A2)
I anticipated the problem. As I wrong before: "it would be wrong to copy
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.
"ottlots" <ott...@gmail.com> 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.
> I printed the trendline equation on the chart.
> The equation is:
> y=5E+16e[to the power of]-3E-04x
> The equation is:
> y=5E+16e[to the power of]-3E-04x
"ottlots" <ott...@gmail.com> 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").
> I must have omitted some important details or I am
> applying this wrong, because the results I am getting
> don't make sense.
"ottlots" <ott...@gmail.com> wrote:
> 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:
> 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.
=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
values are usually too inaccurate to use into an Excel formula".
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.
"ottlots" <ott...@gmail.com> 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.
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
Oct 30, 2012, 5:31:58 PM10/30/12
to
On Friday, October 26, 2012 5:15:22 PM UTC-5, joeu2004 wrote:
> "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 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. "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 me close enought to what I need. After re-reading your original warning, it made sense. Thanks again.
> "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 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. "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 me close enought to what I need. After re-reading your original warning, it made sense. Thanks again.
roond...@gmail.com
Mar 14, 2017, 6:52:18 PM3/14/17
to
So the capital E is always *10 ?
0 new messages