Find volume based on depth in relation to a fill table

You would use

=FORECAST(Depth,B:B,A:A)

Where Depth is the value you know, and columns A and B have the fill table, with depths in A and volumes in B, like

=FORECAST(H7,B:B,A:A)

Where H7 has the depth entry.

I would use a simple vlookup() for this. If your supplied data table looks something like this...

	A	B	C
1	150	0	1
2	8	70	2
3		77.5	3
4		82.5	4
5		87.5	5
6		95	6
7		100	7
8		130	8

A1= depth given
B1=VLOOKUP(A1,$B$1:$C$8,2,1) will return the volume from that table

I will concur with the others that this is either a regression problem or a lookup problem.

The Forecast() function may work, a lot depends on how "linear" the depth vs. fill curve is. For something with an "irregular shape", a straight line function may not be a good choice. There are other regression functions that can be used to obtain a variety of regression curves -- if this is the direction you want to go with the problem.

A lookup function should be straightforward. You may want to use interpolation for values in between the table entries, or perhaps it is adequate to get the "closest" neighbor solution. A lookup solution might be the easier of the two proposed solutions.

From what I gather from the responses, one way forward is to expand the table to cover every possible depth reading and use VLOOKUP but I was hoping there would be a way to avoid this...

I'm not sure that it is exactly necessary to expand the table for every possible reading and use VLOOKUP(). If a "nearest neighbor" kind of result (like you would get with a simple lookup function) is not adequate, you are going to have to consider how you want to interpolate between data points.

If you are up to it, I might suggest Wikipedia's entry on interpolation: http://en.wikipedia.org/wiki/Interpolation

By far the simplest interpolation algorithm to implement (beyond "nearest neighbor" type algorithms) is a linear interpolation. Unfortunately, Excel (unlike Quattro Pro and perhaps other spreadsheet applications) does not provide a built in linear interpolation function. It is relatively easy to program a linear interpolation algorithm into Excel -- especially if you already understand how the lookup functions work. Essentially, linear interpolation consists of a lookup step (to locate the points that bracket the unkown data point) and an algebra step to calculate the desire y value at the given x value. We discuss linear interpolation quite frequently -- if you search the forum, you should find several examples of linear interpolation in action.

The other "easy" interpolation algorithm to implement in Excel will be a curve fitting/regression type algorithm, where you assume a single function that will "fit" all of the points, then derive parameters for that function. Just how easy this one is will depend on how irregular your tank is. If the tank is still relatively simple (approximately spherical or cylindrical or rectangular or other simple shape), then the fitting equation can probably be fairly simple (you would probably start with some geometry before you got into Excel). If the tank is quite irregular, then it will probably be easier to simply resort to a different interpolation algorithm.

FWIW, the four points you have posted do not look very irregular, so this may be fairly easy. From what I can see, either algorithm should work ok (depending on how accurate you feel you need to be).

With your table in A:B, with data starting in A2, and the value of interest (900) in cell D2, you can use

=TREND(OFFSET($B$2,MATCH(D2,$A$2:$A$7,-1),0,2,1),OFFSET($A$2,MATCH(D2,$A$2:$A$7,-1),0,2,1),D2)

Change the 7s to the row number with the last value. This will interpolate between the two values closest to your entry - as long as the table is in descending order by depth.

Try this formula

=IFERROR(VLOOKUP(D2,A:B,2,FALSE),TREND(OFFSET($B$2,MATCH(D2,A:A,-1)-2,0,2,1),OFFSET($A$2,MATCH(D2,A:A,-1)-2,0,2,1),D2))

Here is a workbook with a working example - with your data sorted by depth descending, as in the example.

Interpolate value from list sorted descending.xlsx