CODEWITHSUNDEEP
excelmath
jswanstr
jswanstr

Reputation: 33

Need to calculate a formula

I need some math help, or some excel help if that works better. I have a set of data points and I need to calculate the intermediate data. The table below are the known data points, what I need to know is if I enter 1200 ft, what MEG is available.

FOOTAGE MEG
1000    19.3
2000    20.66
3000    21.328
4000    21.398
5000    20.976
6000    20.155
7000    19.023
8000    17.658
9000    16.133
10,000  14.513
11,000  12.854
12,000  11.208
13,000  9.617
14,000  8.117
15,000  6.736
16,000  5.493
17,000  4.411
18,000  3.487
19,000  2.724
20,000  2.114

I have entered these into excel and proceded to find a formula that their chart believes to be the answer to my questions. The formula they give is

y = 8E-12x3 - 3E-07x2 + 0.0018x + 18.218

This actually gets me really good results for anything under 12k feet. After that the results stray further and further from accurate until I start getting negative numbers after 18k feet.

I tried entering more orders for it to calculate against, but that just made things worse.

Would I be better off splitting the chart in 2 (>10k ft and <10k ft) and using 2 formulas, or is there a good solution available using the whole chart?

Upvotes: 2

Views: 342

Answers (2)

Will
Will

Reputation: 1

Can I suggest you use a small VBA script, which uses linear interpolation to pull out a value from your list: 

Public Function Linterp(Tbl As Range, x As Double) As Variant
     ' linear interpolator / extrapolator
     ' Tbl is a two-column range containing known x, known y, sorted x ascending

    Dim nRow As Long
    Dim iLo As Long, iHi As Long

    nRow = Tbl.Rows.Count
    If nRow < 2 Or Tbl.Columns.Count <> 2 Then
        Linterp = CVErr(xlErrValue)
        Exit Function '-------------------------------------------------------->
    End If

    If x < Tbl(1, 1) Then ' x < xmin, extrapolate from first two entries
        iLo = 1
        iHi = 2
    ElseIf x > Tbl(nRow, 1) Then ' x > xmax, extrapolate from last two entries
        iLo = nRow - 1
        iHi = nRow
    Else
        iLo = Application.Match(x, Application.Index(Tbl, 0, 1), 1)
        If Tbl(iLo, 1) = x Then ' x is exact from table
            Linterp = Tbl(iLo, 2)
            Exit Function '---------------------------------------------------->
        Else ' x is between tabulated values, interpolate
            iHi = iLo + 1
        End If
    End If

    Linterp = Tbl(iLo, 2) + (Tbl(iHi, 2) - Tbl(iLo, 2)) * (x - Tbl(iLo, 1)) / (Tbl(iHi, 1) - Tbl(iLo, 1))     
End Function

You call this from your sheet with something like:

=Linterp(A1:b10, 1200)

You can easily tweak the code to adjust how you want values outside of the range to be handled.

On a slightly different note, you may also be interested in this http://www.codecogs.com/excel_render which can draw out your equations.

Upvotes: 0

user85109
user85109

Reputation:

Precision is tremendously important. In fact, there are several serious problems here with what you have done.

Mere use of coefficients with a single digit of precision will cause terrible problems. Remember that x is as large as 20000. Cubing a number of that size will be a huge number. Now, multiply it by a number on the order of 8e-12, and what do you get?

Oh, by the way, the actual value of those coefficients should be closer to 

[8.38044124105504e-12  -2.95337111670131e-07  0.00176948515975282  18.2176584107327]

So does this make a difference?

8e-12*20000^3
ans =
    64

8.38044124105504e-12*20000^3
ans =
         67.0435299284403

It DOES make a difference, a serious one.

You MIGHT choose to use simple linear interpolation, but the cubic is a bit smoother. Beware extrapolation, as a cubic will do strange things if you try it. In fact though, the cubic polynomial has a significant amount of lack of fit. You can do significantly better using a 4th order polynomial, as long as you are careful to scale the independent variable (footage) by dividing by 10000.

a4 =  -3.02325078929022
a3 =  21.0780945560741
a2 = -46.9692303618201
a1 =  26.3111163470058
a0 =  17.1162276831784

MEG = a0 + a1*footage/10000 + a2*(footage/10000)^2 +
          a3*(footage/10000)^3 + a4*(footage/10000)^4

Note the importance of scaling by 10000 (or at least a number that is chosen to transform your numbers so they are on the order of 1 or so.)

I'd not go much past that point though in terms of increasing the order of the fit.

Upvotes: 3

Related Questions