# Interpolation Table and Calculator

1. ## 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

2. ## 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.

3. ## Re: Interpolation Table and Calculator

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

Thanks again

4. ## 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. ## 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. ## 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. ## 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

8. ## 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. ## 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. ## 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. ## Re: Interpolation Table and Calculator

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

12. ## Re: Interpolation Table and Calculator

Man this is perfect!

Thanks so much. Excellent!

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