Help with Interpolation
Posted by Patrick Kilgore on March 15, 2001 12:23 PM
I have a worksheet that calculates stability parameters for a ship based on the amount of water stored in the ship's ballast tanks. I would like to create a function that will read the water level in a tank from this sheet and return quantities such as volume and weight of the water in the tank. The function would read water level and volume data stored in other worksheets, interpolate based on water level, and write the result to the original sheet. My question is this: How do you tell Excel to look for the two values to interpolate between? Once Excel finds the two tank levels on the data sheet that most closely bracket the tank level of interest on the original sheet, interpolating results should be fairly straightforward.
Posted by Mark W. on March 15, 2001 12:57 PM
Interpolate using regression?
Let's say that A1:A6 contains...
{10
;15
;""
;""
;45
;65}
The "gap" (cells A3:A4) could be filled using the
formula, {=TREND({15;45},{2;5},{3;4})}. Is that
what you're trying to do?
Posted by Tony Sorace on March 16, 2001 3:05 AM
Re: Interpolate using regression?
what type of ship is it? all tanks have "Ullage or sounding" tables, expect of course on shore where they are called "Strapping" tables, I have done a few "ullage tables" for ships and all I did was to copy the table at every "strapping" point, you have to take account for the shape of the hull, so here, from a non excel expert, but ex seaman is the formula that I use, and is accurate to less than half a barrel, if American, or less than half a cubic metre if not.
first make a column for observed ullage, and a 2nd column for the volume then in the third column place this formula, =ROUND(((E3-E2)/(D3-D2)),4)
if a wing tank, you will need to make entries for every 5-10 cm for the first 1 or 2 metres, then as it goes down the "Stright" part of the tank every metre should be enough, then for the last couple of metres you will need to revert back to every 10 cm or so to take account of the shape of the hull, after the tank has been completed highlight the whole lot and name it, the example here is "centre1" and so on for each tank.
on another sheet select a cell lets say "a1" write Ullage and then "b1" volume, from here you would use "a2" to enter the ullage and in "b2" enter this formula:-
=ROUND(((($a$2-VLOOKUP($a$2,centre1,1))*(VLOOKUP($a$2,centre1,3)*1))+(VLOOKUP($a$2,centre1,2)*1)),2)
this should then give the answer you are looking for.
I've also made "user designed" functions for tables 54 a and b 6 a and b tables 3,11,12,13,6 and 52 these I hope to sell. The API sell theirs for 1,000 dollars a pop, so you can see why I don't want to just give them away, need to try and supplement my pension!!
hope this is not to long
Posted by Mark W. on March 16, 2001 5:01 AM
Wow! Neat stuff.
Posted by Aladin Akyurek on March 16, 2001 10:51 AM
Yep. Fascinating domain knowledge...
...embodied in computations. Yes, it's neat.
