+ Reply to Thread
Results 1 to 12 of 12

Interpolation Table and Calculator

  1. #1
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Interpolation Table and Calculator

    First, I apologize. I am new here and haven't properly introduced myself, nor have I spent a great amount of time searching for the answers to the questions for which I am about to ask, however a can give a quick answer to both.

    I work aboard a deep sea offshore driling rig. I am here now. Our internet is extremely slow and even then its is only available to myself briefly throughout the course of a day.

    I have a table of values which corresponds strappings (measurements by measuring tape) to volume and weight measurements. My department is asked to provide conversions for these strappings regularly. Because we all failed math and ended up out here on a drilling rig in the middle of the ocean, no two of us can regularly provide consistent conversions, which results in very aggravated supervisors.

    What I would like to do is provide a very simple calculator which would only require two inputs (strappings in inches and product) and spit out the interpolated results for each of other columns. Additionally for cement, I would like a column which converts the metric tonnage column into sacks of cement. In this case we generally use the following calculation: {resultant tons x 2204.6 (metric ton to lbs)}/94lbs (pounds in a sack of cement = sacks.

    Ideally, so as not cause unnecessary confusion for my marginally slow collegues and myself, I would either hide the table completely or place it on a separate worksheet. Only the calculator would be visible. I am willing to do all the leg work on this I just need to be pointed in the right direction. Again, I apologize if this has been covered earlier. I am not a complete forum newbie and I know that there is a lot of spite for those that do not know how or are not willing to use the search function. I hope you can find it in your spreadsheet hearts to just this once, give me bit of a pass, I am already getting looks for how long I've been on the internet.

    Thanks,
    RigMaster

    Bulk Barite Cement & Gel Conversion Table 16 december 2016.xlsx
    Last edited by RigMaster; 12-17-2015 at 05:15 PM. Reason: additional information

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Interpolation Table and Calculator

    Welcome to the board.

    The first favor is one you can do for yourself: post a workbook rather than a picture. No one is likely to be inclined to type a bunch of data as a prelude to helping.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Re: Interpolation Table and Calculator

    Thank you! Apologies, I attached it via the insert image function. I'd missed it earlier.

    Thanks again

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Interpolation Table and Calculator

    Let me rephrase what you said to see if I understand the requirement.

    I am a bit confused: the measurement in the first column is in inches, the measurement in the second column is in feet and the measurement in the third column is in meters and somehow this is converted into cubic meters.

    Not only that, but I converted all to meters and came up with nothing at all like the figure in column 4.

    As best I can tell from the picture, the item being considered is an irregular shape: it is not a box-like rectangular solid. So it must be a combinations of cylinders and cones and whatnot.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Interpolation Table and Calculator

    It would help a lot if we knew what the measurements meant for example are they diameters, radii or lengths. I have a feeling that there is a pure mathematical formula to compute the volume.

    Failing that, can you tell us how you are doing the interpolations now?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Interpolation Table and Calculator

    I figured it out. The three columns are actually the same measurement in inches, feet and meters.

    I took a handful of point and plotted them. They are all pretty much in a straight line except for the very first measurement. In inches the formula is
    -0.23019 * inches + 8618014.

    I'd have to make a separate guess on between the first and second points.

    It would sill be better if we could have the full table in excel

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Interpolation Table and Calculator

    If linear interpolation is sufficient,

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    2
    Barite
    Bentonite
    Cement
    3
    Hgt [m]
    T/m^3
    2.16
    0.96
    1.51
    G3 and across: =PERCENTILE(G$6:G$40, 1 - PERCENTRANK($D$6:$D$40, $D$3, 6))
    4
    4.42
    97.777
    43.456
    68.353
    5
    6
    Hgt [in]
    Hgt [ft]
    Hgt [m]
    Vol [m^3]
    [T]
    [T]
    [T]
    7
    29.52
    2.46
    0.75
    88.0
    190.080
    84.480
    132.880
    8
    39.37
    3.28
    1.00
    77.7
    167.832
    74.592
    117.327
    9
    49.21
    4.10
    1.25
    75.3
    162.648
    72.288
    113.703
    10
    59.05
    4.92
    1.50
    72.9
    157.464
    69.984
    110.079
    11
    68.89
    5.74
    1.75
    70.6
    152.496
    67.776
    106.606
    12
    78.74
    6.56
    2.00
    68.2
    147.312
    65.472
    102.982
    13
    88.58
    7.38
    2.25
    65.8
    142.128
    63.168
    99.358
    14
    98.42
    8.20
    2.50
    63.5
    137.160
    60.960
    95.885
    15
    108.26
    9.02
    2.75
    61.1
    131.976
    58.656
    92.261
    16
    118.11
    9.84
    3.00
    58.7
    126.792
    56.352
    88.637
    17
    127.95
    10.66
    3.25
    56.4
    121.824
    54.144
    85.164
    18
    137.79
    11.48
    3.50
    54.0
    116.640
    51.840
    81.540
    19
    147.63
    12.30
    3.75
    51.6
    111.456
    49.536
    77.916
    20
    157.48
    13.12
    4.00
    49.2
    106.272
    47.232
    74.292
    21
    167.32
    13.94
    4.25
    46.9
    101.304
    45.024
    70.819
    22
    177.16
    14.76
    4.50
    44.5
    96.120
    42.720
    67.195
    23
    187.00
    15.58
    4.75
    42.1
    90.936
    40.416
    63.571
    24
    196.85
    16.40
    5.00
    39.8
    85.968
    38.208
    60.098
    25
    206.69
    17.22
    5.25
    37.4
    80.784
    35.904
    56.474
    26
    216.53
    18.04
    5.50
    35.0
    75.600
    33.600
    52.850
    27
    226.37
    18.86
    5.75
    32.6
    70.416
    31.296
    49.226
    28
    236.22
    19.69
    6.00
    30.3
    65.448
    29.088
    45.753
    29
    246.06
    20.51
    6.25
    27.9
    60.264
    26.784
    42.129
    30
    255.90
    21.33
    6.50
    25.5
    55.080
    24.480
    38.505
    31
    265.74
    22.15
    6.75
    23.2
    50.112
    22.272
    35.032
    32
    275.59
    22.97
    7.00
    20.8
    44.928
    19.968
    31.408
    33
    285.43
    23.79
    7.25
    18.4
    39.744
    17.664
    27.784
    34
    295.27
    24.61
    7.50
    16.0
    34.560
    15.360
    24.160
    35
    305.11
    25.43
    7.75
    13.7
    29.592
    13.152
    20.687
    36
    314.96
    26.25
    8.00
    11.3
    24.408
    10.848
    17.063
    37
    324.80
    27.07
    8.25
    8.9
    19.224
    8.544
    13.439
    38
    334.64
    27.89
    8.50
    6.9
    14.904
    6.624
    10.419
    39
    344.48
    28.71
    8.75
    5.9
    12.744
    5.664
    8.909
    40
    354.33
    29.53
    9.00
    4.9
    10.584
    4.704
    7.399
    41
    364.17
    30.35
    9.25
    4.0
    8.640
    3.840
    6.040
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Re: Interpolation Table and Calculator

    Right now we use the table and do a simple linear interpolation, it's not difficult, except that it leaves some margin of error from user's perspective (the math doesn't lie obviously).

    The first three columns are distance measurements. This measurement is obtained by a derrickhand. He uses a long tape like measuring device (called a sounding tape) to measure the distance down from the top of a tank to the top of product inside the tank. In other words he's measuring the remaining (empty) space. This sometimes called ullage. Depending on whether the derrickhand provides us the distance measurement in inches, feet or meters we enter either the first, second, or third columns.

    The rest is simple interpolation. If volume of space remaining in tank is requested we find the corresponding above/below figure in the "Calculated Volume of remaining product" column and interpolate.

    If weight of product is requested we find out the product, then find the corresponding above/below figure in the appropriate (Barite, Bentonite, or Cement) column and interpolate.

    Frequently a third calculation is required to convert the product weight in metric tons (which is what the table reads) to Sacks. Sacks weights are different for different products and classes of products, but the most frequently requested unit is sacks of Class H cement. To convert metric tons to sacs of cement we multiply the number found (via the table and interpolation, as above) by 2204.6 (lbs per metric ton), then we divide that number by 94 (lbs of cement in a bag). The value found is then for # of sacks of cement.

    Hope this makes sense.

  9. #9
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Re: Interpolation Table and Calculator

    Leave it to a Texan!

    Thanks, thats looking great.

    As note, I believe everything is linear except for the first one or values and the last one or two values. The thanks are not simple geometry, but rather shaped like grain silos. The measurements would therefore be linear for the everything except the very top and bottom, which are rounded.

  10. #10
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Re: Interpolation Table and Calculator

    Is there an easy way to add an inches cell and a feet cell so that the user might be able to input any one of the three values?

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Interpolation Table and Calculator

    Here's my entry - had trouble uploading the attachment.

    Enter the unit of measure in cell C2 and then the measurement in cell B2.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-17-2015
    Location
    GOM
    MS-Off Ver
    2010
    Posts
    6

    Re: Interpolation Table and Calculator

    Man this is perfect!

    Thanks so much. Excellent!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 3d interpolation of a table
    By shim85 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2015, 09:11 AM
  2. [SOLVED] Sub for table interpolation
    By potga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2014, 04:34 PM
  3. Two-way table lookup with interpolation
    By galayfe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2013, 05:56 PM
  4. interpolation in multiple way table
    By nessus123 in forum Excel General
    Replies: 2
    Last Post: 11-30-2012, 08:01 AM
  5. Table lookups for interpolation
    By jrquebe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2007, 12:08 AM
  6. linear interpolation 2-way table
    By mike0123m in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-28-2007, 02:09 PM
  7. table interpolation
    By mervincris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2006, 08:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1