# trendline coefficient problem

#### Deladier

##### Board Regular
Hi experts, I have some data in ranges: C2:C7,B2:B7, values are:
_B________C___
100 100,41
200 200,842
300 301,274
400 401,706
500 502,138
600 602,57

and resulting Trendline ecuation in the graph is the following:

y = 6.50521E-19x3 - 6.39246E-16x2 + 1.00432E+00x - 2.20000E-02
with R2 = 1,00000E+00

I usually obtain directly the coefficients (6.50521E-19, -6.39246E-16, 1.00432E+00, -2.20000E-02) using something

like this:

=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),1)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),2)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),3)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),4)

(each value in a different cell)

but I realized that numbers with an exponent so small (E-16, E-19 for this case) the result coefficient is zero with the formula.

Is there some way to see the correct small coefficient numbers (6.50521E-19 and -6.39246E-16 for this case), just like the graph shows?

Thanks in advance.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Deladier

##### Board Regular
Okay, sorry, I understand why it seems so difficult to solve it, let me express the thread in other way more comprehensible:

Hi experts, I have some data in ranges C2:C7, and B2:B7. Values are:

for range B2:B7 we have this:
B2: 100
B3: 200
B4: 300
B5: 400
B6: 500
B7: 600

and for range C2:C7 we have this:
C2: 100.41
C3: 200.842
C4: 301.274
C5: 401.706
C6: 502.138
C7: 602.57

and resulting Trendline ecuation in the graph is the following:

y = 6.50521E-19x3 - 6.39246E-16x2 + 1.00432E+00x - 2.20000E-02
with R2 = 1,00000E+00

I usually obtain directly the coefficients (6.50521E-19, -6.39246E-16, 1.00432E+00, -2.20000E-02) using something

like this:

=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),1)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),2)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),3)
=INDEX(LINEST(C2:C7,B2:B7^{1,2,3}),4)

(each value in a different cell)

but I realized that numbers with an exponent so small (E-16, E-19 for this case) the result coefficient is zero with the formula.

Is there some way to see the correct small coefficient numbers (6.50521E-19 and -6.39246E-16 for this case), just like the graph shows?

Thanks in advance.

#### Deladier

##### Board Regular
I only want to be able to see a very small number (that it is practically zero, but not zero, per example: 6.50521E-19) using the formula but I don't want just a simple zero instead.

Or is there some other way to get the coefficients without using LINEST function and be able to show even the smallest numbers?

Thanks in advance.

Replies
3
Views
347
Replies
7
Views
316
Replies
10
Views
601
Replies
3
Views
392
Replies
3
Views
254

Threads
1,191,501
Messages
5,986,927
Members
440,067
Latest member
Swatts1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back