Need Formula

Rail · Feb 11, 2004

Hello everyone!

I would like to figure out the formula for the given two columns of data.

The data:

X Axis = 0.5|0.75|1.0|1.5|2.5|3.5|4.5|7.5|15.0|25.0|35.0|45.0
(Numbers separated by vertical lines for ease of viewing)
Y Axis = (number's too large to list here...see below

1.509E-09 (this number corresponds to the 0.5 column)
1.111E-09 (this number corresponds to the 0.75 column)
8.536E-10 (ETC...)
5.001E-10
2.647E-10
1.788E-10
1.206E-10
5.231E-11
1.711E-11
1.248E-11
3.737E-11
6.706E-11

Should be a reverse exponential curve! I would like the formula to figure out the value that falls between the given numbers. Example: If I have a number (x axis) that falls between 7.5 & 15...say 10.1, I would like the formula to figure out the y-axis number...the number should fall between these to numbers: 5.231E-11 (which represents the 7.5) & 1.711E-11 (which represents the 15.0). I would like to set this formula up in Excel in a way that I could just type in a value and it will spit out the y-axis number.
I had a dos program that used to extrapolate these numbers but it's an old program and wont work on my computer.

I have about 15 sets of numbers that will have a different formula...so if you have time could you go over how to set up the data to get the formula. I want to thank you in advance for the effort on your part to help me out. Much appreciative!

SIXTH SENSE · Feb 12, 2004

hi!
My first post looks for the exact value and error on every values not found.
but it is 100% accurate on known values.
If you are interested with the formula itself not the answer then the the power trend line give a better fit than the exponential trend line
See below.

Book1

C

D

E

F

G

H

1

y=6E-10x^(-0.9627)

error

y=3E-10*e^(-0.0708*x)

error

interpolation

error

2

3

4

5

6

7

8

9

10

11

12

13

Sheet3

SIXTH SENSE · Feb 12, 2004

Take a look at this if you are only interested with the values
that are not known with in the given values.
I think this gives better interpolation though linear in nature.

See below!

Book1

J

K

L

M

2

lookfor

interpolation

y=6E-10x^(-0.9627)

y=3E-10*e^(-0.0708*x)

3

4

1

8.536E-10

5

1.25

6

7

8

Sheet3

Rail · Feb 12, 2004

sixth sense, Thanks again for your reply!

your match works great if my value that I input is exactly the same as one of the original points on the graph...like the 0.5, 0.75, 1.0, 1.5 etc...

But if the value is not one of the original numbers the formula will have to interpolate it! I hope I can explain better..please forgive me...lets say we enter these numbers into the A column (our formula will be in the B column).

if you you should
input get
A B
1.5 5.001E-10
2.5 2.647E-10
but if you put in a number say between 1.5 & 2.5....for instance 2.2 the answer should give a value between 5.001E-10 and 2.647E-10 (ie interpolated from graph). Something like 3.000E-10 (not a valid number but used it to illustrate what the number should look like). I like to put in the original numbers every now & then to check the formula....so if the 1.5 and the 2.5 numbers are interpolated correctly then I know the other numbers should be on. What are your thoughts?

Rail · Feb 12, 2004

yes...excellent...your interpolation column looks like what I want...how do I put in that formula?

Rail · Feb 12, 2004

After I looked at your number a little closer...in your extrapolated column...it looks like you just split the difference between the two values....the exponential curve doesn't work like that. It would work with a linear function...but not here. The number has to be extrapolated from the exponential curve formula.

SIXTH SENSE · Feb 12, 2004

Rail said:
After I looked at your number a little closer...in your extrapolated column...it looks like you just split the difference between the two values....the exponential curve doesn't work like that. It would work with a linear function...but not here. The number has to be extrapolated from the exponential curve formula.

Hi Rail!
its a complicated program to make extrapolation(to get values outside the known values) errors are huge. but there are a lot of algorithm for this. the linear interpolation between known values gives a fair value and its simple.
it assumes that the two consecutive points are connected by a line, thus
the unknown one lies in the said line.
Ill pm you if ill find back my interpolation program.
It has been 5 years that I didnt use that. In my actual practice as Engineer, that is the exact proces that gives me fair value with less error.
But if you want to be accurate enough then give me some time.

have a good day!

SIXTH SENSE · Feb 13, 2004

hi!
As I have posted earlier, interpolation is complicated algorithm in numerical anlysis. But more or less I have one code in here. this uses the spline method of interpolation.
This has a very high acuraccy on values within the given data.
I have not fully tested this on values outside it.
see below.

Book1

A

B

C

D

E

F

4

Xvalues

Yvalue

Splineinterpolation

Error

look?for

ans

5

0.5

1.5090E-09

6

0.75

1.1110E-09

1.111E-09

0.00%

8

4.7522E-11

7

1

8.5360E-10

8

1.5

5.0010E-10

5.001E-10

0.00%

9

2.5

2.6470E-10

2.647E-10

0.00%

10

3.5

1.7880E-10

1.788E-10

0.00%

11

4.5

1.2060E-10

1.206E-10

0.00%

12

7.5

5.2310E-11

5.231E-11

0.00%

13

15

1.7110E-11

1.711E-11

0.00%

14

25

1.2480E-11

1.248E-11

0.00%

15

35

3.7370E-11

3.737E-11

0.00%

16

45

6.7060E-11

6.706E-11

0.00%

Sheet1

the code..........Dim xRaw()AsDoubleDim yRaw()AsDoubleDim y2Raw()AsDoubleFunction Interpolate(XAs Range, YAs Range, LookForAsDouble)AsDouble
 Dim iAsLong
 Dim vAsDouble
 Dim yp1AsDouble
 Dim ypnAsDouble
 Dim cAs Range
 Dim NRowAsInteger

NRow = X.Cells.CountReDim xRaw(NRow - 1)AsDoubleReDim yRaw(NRow - 1)AsDoubleReDim y2Raw(NRow - 1)AsDouble

 yp1 = 1E+30
 ypn = 1E+30
i = 0
 ForEach cIn X
 xRaw(i) = c
 i = i + 1
 Next c
i = 0
 ForEach cIn Y
 yRaw(i) = c
 i = i + 1
 Next c
 SplineCompute2nDerivative xRaw, yRaw, X.Cells.Count, yp1, ypn, y2Raw
 Interpolate = Splint(xRaw, yRaw, y2Raw, X.Cells.Count, LookFor)EndFunctionPrivateFunction Splint(xaAsVariant, yaAsVariant, y2aAsVariant, nAsLong,ByVal XAsDouble)AsDouble

 Dim kloAsLong, khiAsLong, kAsLong
 Dim hAsDouble, bAsDouble, aAsDouble

 klo = 0
 khi = n - 1
 While khi - klo > 1
 k = (khi + klo) / 2
 If xa(k) > XThen khi = k Else: klo = k
 Wend
 h = xa(khi) - xa(klo)
 If h = 0#Then MsgBox ("Bad xa input to routine Splint"):ExitFunction'error handling for x1=x2
 a = (xa(khi) - X) / h
 b = (X - xa(klo)) / h
 Splint = a * ya(klo) + b * ya(khi) + ((a * a * a - a) * y2a(klo) + (b * b * b - b) * y2a(khi)) * (h * h) / 6#EndFunctionPrivateSub SplineCompute2nDerivative(XAsVariant, YAsVariant, nAsLong, yp1AsDouble, ypnAsDouble, y2AsVariant)

 Dim iAsLong, kAsLong
 Dim pAsDouble, qnAsDouble, sigAsDouble, unAsDouble
 ReDim u(n - 1)AsDouble

 If yp1 > 9.9E+29Then
 y2(0) = 0#
 u(0) = 0#
 Else
 y2(0) = -0.5
 u(0) = (3# / (X(1) - X(0))) * ((Y(1) - Y(0)) / (X(1) - X(0)) - yp1)
 EndIf

 For i = 1To n - 2
 sig = (X(i) - X(i - 1)) / (X(i + 1) - X(i - 1))
 p = sig * y2(i - 1) + 2#
 y2(i) = (sig - 1#) / p
 u(i) = (Y(i + 1) - Y(i)) / (X(i + 1) - X(i)) - (Y(i) - Y(i - 1)) / (X(i) - X(i - 1))
 u(i) = (6# * u(i) / (X(i + 1) - X(i - 1)) - sig * u(i - 1)) / p
 Next i
 If ypn > 9.9E+29Then
 qn = 0
 un = 0#
 Else
 qn = 0.5
 un = (3# / (X

- X(n - 1))) * (ypn - (Y

- Y(n - 1)) / (X

- X(n - 1)))
 EndIf
 y2(n - 1) = (un - qn * u(n - 1)) / (qn * y2(n - 1) + 1#)
 For k = n - 2To 0Step -1
 y2(k) = y2(k) * y2(k + 1) + u(k)
 NextEndSub

Need Formula

Rail

New Member

SIXTH SENSE

Well-known Member

Excel Facts

SIXTH SENSE

Well-known Member

Rail

New Member

Rail

New Member

Rail

New Member

SIXTH SENSE

Well-known Member

SIXTH SENSE

Well-known Member

Similar threads

Forum statistics

Share this page

Need Formula

New Member

Well-known Member

Excel Facts

Well-known Member

New Member

New Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock