Help with Exponential Forumla

[olismith89]

Hi there,

I am trying to create an exponential formula but I can't wrap my head around it.

Essentially, I want to be able to enter X (see below) into cell A1 and have the result appear in A2.

Nerdy background for my problem - it's for a D&D game where characters will spend X amount of time within a separate dimension. With each hour they spend, the time in their home dimension advances exponentially.
1 hour inside = 1 hour outside
+1
2 hours inside = 2 hours outside
+2
3 hours inside = 4 hours outside
+3
4 hours inside = 7 hours outside
+4
5 hours inside = 11 hours outside
Etc.

Each hour, the time on the outside increases exponentially starting at +1, then +2, +3, +4 etc. So if they spent 8 hours inside, it would be 1+2+4+7+11+16+22+29 = 92 hours.

I want to be able to enter X as the amount of time they spent inside this dimension and have the formula work out the exponential and total it to show how much time has passed in their home dimension.

I'm ok at maths but not very good with exponentials so I can't even work out a real-world formula for my problem. Any maths wizards who know a thing or two about excel, please help!

Hopefully the example makes sense.

 

[BSALV]

Map1
	A	B	C	D
1			inside	outside
2	outside		8	92
3	1
4	2
5	4
6	7
7	11
8	16
9	22
10	29
11
Blad3

Cell Formulas
Range	Formula
D2	D2	=SUM(OFFSET(Tabel1[[#Headers],[outside]],1,,C2,))

 

[shknbk2]

Another option based solely on polynomial equation.

The equation is 1/2 x^2 - 1/2 x + 1. How I found this is in my next reply below.

Book1
	A	B
1	1	1
2	2	2
3	3	4
4	4	7
5	5	11
6	6	16
7	7	22
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=0.5*A1^2-0.5*A1+1

 

[olismith89]

Map1
	A	B	C	D
1			inside	outside
2	outside		8	92
3	1
4	2
5	4
6	7
7	11
8	16
9	22
10	29
11
Blad3

Cell Formulas
Range	Formula
D2	D2	=SUM(OFFSET(Tabel1[[#Headers],[outside]],1,,C2,))

This doesn't seem to work for me. It may be because my data isn't in a specific table (& when I try to put it one the table isn't formatting how I want it to). Any way the formula provided can be altered to use a cell range, or does the reference have to be a table?

 

[olismith89]

Another option based solely on polynomial equation.

The equation is 1/2 x^2 - 1/2 x + 1. How I found this is in my next reply below.

Book1
	A	B
1	1	1
2	2	2
3	3	4
4	4	7
5	5	11
6	6	16
7	7	22
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=0.5*A1^2-0.5*A1+1

Many thanks for the formula, that's half the problem sorted!

 

[BSALV]

D2 = SUM(OFFSET($a$3,,,C2,))

 

[shknbk2]

I found this trick a few months back because I was interested to see if I could find the equation for the sum of squares. Merely curiosity. This goes: 1 + 4 + 9 + 16 + ... So the list of values is 0, 1, 5, 13 for 0, 1, 2, 3.

What I found is that to know the order of the equation (x, x^2, x^3, etc.), you can find the differences between the sum values, and those values, etc, until a single number shows up. So for the sum of squares, the first list of differences is 1(difference between 0 and 1 or the difference between 0^2 and 0^2 + 1^2), 4 (1 to 5), 9 (5 to 13). Since these numbers aren't the same (1,4,9, etc), the next difference would be 3,5,7 (1 to 4, 4 to 9, 9 to 16). Since these aren't the same, the next difference is 2,2,2. Because they are the same, the formula would be based on x^3.

I did the same for your numbers.

Book1
	A	B	C	D
1	Input	Output	First difference	Second difference
2	1	1	0
3	2	2	1	1
4	3	4	2	1
5	4	7	3	1
6	5	11	4	1
7	6	16	5	1
8	7	22	6	1
9	8	29	7	1
10	9	37	8	1
Sheet1

Since the second difference is all the same, it is a quadratic equation. Then, it's a matter of solving for 3 unknowns (ax^2+bx+c):
a1^2+b+c=1
a2^2+2b+c=2
a3^2+3b+c=4.
Put these into an online calculator (or by hand) and you get 0.5x^2-0.5x+1.

 

[BSALV]

and the formula for the sum ...

 

[shknbk2]

Ok. I'm not so smart today. I gave the line by line equation and not the sum. Here's the update.

The sum equation is 1/6 x^3 + 5/6 x.

Book1
	A	B	C	D	E	F	G
1	Input	Output	Sum	First difference	Second difference	Third difference	Sum based on equation
2	1	1	1	1			1
3	2	2	3	2	1	1	3
4	3	4	7	4	2	1	7
5	4	7	14	7	3	1	14
6	5	11	25	11	4	1	25
7	6	16	41	16	5	1	41
Sheet1

Cell Formulas
Range	Formula
C2:D2	C2	=B2
G2:G7	G2	=(1/6)*A2^3 +(5/6)*A2
B3:B7	B3	=B2+A2
C3:C7	C3	=B3+C2
D3:F7	D3	=C3-C2

 

[Eric W]

That technique is often called forward divided differences. I'm pretty impressed you figured it out. If you're a real math nerd, here are some links that talk about it:

Newton polynomial - Wikipedia

en.wikipedia.org