Convert a Power Trendline into Data

Cazzy B

New Member
Joined
Oct 6, 2014
Messages
7
Hi - hoping someone can help me, I'm really struggling!

I have some data which I have plotted into a graph and created a power trend line.

I want to have the data that creates the power trend line in a table.

I have found formulas - but I just don't seem to be smart enough to put them into practice. Can anyone help me?

This is my original data;

DATA
QuantityPrice
10070.07
200101.33
300215.6
400280.19
500283.51
50062.52
1000457.65
1000457.65
1000381.83
1000295.37
1000280.19
1500338.49
1500338.49
2000458.15
2000352.9
2000332.02
2000330.03
2550267.34
3000717.61
3000555.17
3000467.85
40001020.9
4000773.65
4900562.16
6000365.44
8000512.05
500001293.6
<colgroup><col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <tbody> </tbody>

This is my graph with power trend line.

-- removed inline image ---


This is the table that I want to complete with the trend line data (up to 50,000)

QuantityPrice
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

This is the equation that I found;
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
<colgroup><col width="46" style="width: 35pt;" span="2"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="46" style="width: 35pt;" span="3"> <tbody> </tbody>

Can anyone help me put this into practice?
 

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).
Welcome to MrExcel.

Formula in E2 copied down:


Excel 2010
ABCDE
1QuantityPriceQuantityPrice
210070.07100101.3687
3200101.33101101.8229
4300215.6102102.2746
5400280.19103102.7239
6500283.51104103.1708
750062.52105103.6154
81000457.65106104.0576
91000457.65107104.4975
101000381.83108104.9352
111000295.37109105.3707
121000280.19110105.8039
131500338.49111106.235
141500338.49112106.664
152000458.15113107.0908
162000352.9114107.5156
172000332.02115107.9383
182000330.03116108.359
192550267.34117108.7778
203000717.61118109.1945
213000555.17119109.6093
223000467.85120110.0222
2340001020.9121110.4332
244000773.65122110.8424
254900562.16123111.2497
266000365.44124111.6551
278000512.05125112.0588
28500001293.6126112.4607
29127112.8609
Sheet1
Cell Formulas
RangeFormula
E2=EXP(INDEX(LINEST(LN(B$2:B$28),LN(A$2:A$28),,),1,2))*D2^INDEX(LINEST(LN(B$2:B$28),LN(A$2:A$28),,),1)
 
Upvote 0
Thank you so much! Perfect response - better than expected!!!! :) :):)

Welcome to MrExcel.

Formula in E2 copied down:

Excel 2010
ABCDE
1QuantityPriceQuantityPrice
210070.07100101.3687
3200101.33101101.8229
4300215.6102102.2746
5400280.19103102.7239
6500283.51104103.1708
750062.52105103.6154
81000457.65106104.0576
91000457.65107104.4975
101000381.83108104.9352
111000295.37109105.3707
121000280.19110105.8039
131500338.49111106.235
141500338.49112106.664
152000458.15113107.0908
162000352.9114107.5156
172000332.02115107.9383
182000330.03116108.359
192550267.34117108.7778
203000717.61118109.1945
213000555.17119109.6093
223000467.85120110.0222
2340001020.9121110.4332
244000773.65122110.8424
254900562.16123111.2497
266000365.44124111.6551
278000512.05125112.0588
28500001293.6126112.4607
29127112.8609

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=EXP(INDEX(LINEST(LN(B$2:B$28),LN(A$2:A$28),,),1,2))*D2^INDEX(LINEST(LN(B$2:B$28),LN(A$2:A$28),,),1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi
Welcome to the board

Another option, in E2:

=EXP(TREND(LN($B$2:$B$28),LN($A$2:$A$28),LN(D2)))

Copy down
 
Upvote 0
Thanks Guys - all help greatly appreciated :)
I am now trying to work out the exponential trend line.... still cant seem to master it.... I have the equation but when I drop it in excel it returns #VALUE!


Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

I removed the = so you could see my formula.

Any ideas?

ABCDE
1QuantityPrice QuantityPrice
210070.07 100EXP(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1,2))*E^(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1)*D2)
3200101.33 101
4300215.6 102
5400280.19 103
6500283.51 104
750062.52 105
81000457.65 106
91000457.65 107
101000381.83 108
111000295.37 109
121000280.19 110
131500338.49 111
141500338.49 112
152000458.15 113
162000352.9 114
172000332.02 115
182000330.03 116
192550267.34 117
203000717.61 118
213000555.17 119
223000467.85 120
2340001020.9 121
244000773.65 122
254900562.16 123
266000365.44 124
278000512.05 125
28500001293.6 126
29 127
<colgroup><col width="64" style="width: 48pt;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="64" style="width: 48pt;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="472" style="width: 354pt; mso-width-source: userset; mso-width-alt: 17261;"> <tbody> </tbody>
 
Upvote 0
Thank you - although is still giving me a #VALUE!
I'm not sure what I am doing wrong....

=EXP(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1,2))*EXP(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1)*D2)
 
Upvote 0
Working here:


Excel 2010
ABCDE
1QuantityPriceQuantityPrice
210070.07100307.1043
3200101.331000316.9589
4300215.630000876.9903
5400280.19500001769.362
6500283.51
750062.52
81000457.65
91000457.65
101000381.83
111000295.37
121000280.19
131500338.49
141500338.49
152000458.15
162000352.9
172000332.02
182000330.03
192550267.34
203000717.61
213000555.17
223000467.85
2340001020.9
244000773.65
254900562.16
266000365.44
278000512.05
28500001293.6
Sheet2
Cell Formulas
RangeFormula
E2=EXP(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1,2))*EXP(INDEX(LINEST(LN(B$2:B$28),A$2:A$28),1)*D2)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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

Which adblocker are you using?

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
Back
Top