How to generate data points dynamically from power trendline

[JenniferMurphy]

I have some data that I know follows a power curve. I have plotted the data, generated a trendline, and have the power function displayed on the chart. Here's the current set of data and the displayed trendline equation. The value "0.01" in H2 is a temporary value because Excel's power function curve fitting algorithm cannot handle zeroes. (sigh)

[TABLE="class: grid, width: 517"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]N[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Actual Tally[/TD]
[TD="align: center"]122[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.01[/TD]
[/TR]
</tbody>[/TABLE]

y=798.59e-1.371x

As I run each trial, I add to the corresponding tally. For example, if the next trial results in a Value of "3", I will increment D2 from 14 to 15.

I would like to add a new row (3) that wold show the expected tally as computed by the trendline. And I would like that data to be dynamically and automatically updated when I update a value.


[TABLE="class: grid, width: 517"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]N[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Actual Tally[/TD]
[TD="align: center"]122[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.01[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Expected Tally[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]??[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way to do this that might be simple enough for me to understand?

 

[RoryA]

Try this:

Excel 2010
	A	B	C	D	E	F	G	H
1	N	1	2	3	4	5	6	7
2	Actual Tally	122	43	14	6	1	1	0.01
3
4	Expected:	420.7061	26.72079	5.328114	1.697149	0.698764	0.338411	0.183321
5	c	420.7061
6	b	-3.97678
Sheet2

Cell Formulas
Range	Formula
B4	=$B$5*B1^$B$6
B5	=EXP(INDEX(LINEST(LN($B$2:$H$2),LN($B$1:$H$1),,),1,2))
B6	=INDEX(LINEST(LN($B$2:$H$2),LN($B$1:$H$1),,),1)
C4	=$B$5*C1^$B$6
D4	=$B$5*D1^$B$6
E4	=$B$5*E1^$B$6
F4	=$B$5*F1^$B$6
G4	=$B$5*G1^$B$6
H4	=$B$5*H1^$B$6

 

[RoryA]

PS You may find this useful: Spreadsheet Page Excel Tips: Chart Trendline Formulas

 

[JenniferMurphy]

Try this:

Wow. That works! I would never have figured that out by myself. Thanks so much.

 

[JenniferMurphy]

PS You may find this useful: Spreadsheet Page Excel Tips: Chart Trendline Formulas

Very helpful, thanks.

I notice that I misled you by saying that I was fitting the data to a power curve. It is actually an exponential curve. Once I got that straightened out with the help of this page, it works perfectly. Very much appreciated.