Formula: calculate liquid volume

[pujo]

I have a tote tank with a known capacity, as well as the base size. (42" x 48")
Manufacture provided a strapping report however, I need to calculate not only the whole numbers, I also need the .25, .50, .75

Goal is to type in the cell a measurement (indicated inched) and calculate the gallons remaining.

I have tried a few different formulas however, my attempts do not line up with the provided strapping report.

Hope someone can assist with the formula.

Thanks!

Strapping report image attached.

 

[jdellasala]

It would have helped to post the table as data rather than a picture. I seem to have been able to pull in the Gallons part fairly accurately, but there are a lot of discrepancies.

Book1
	A	B	C	D
1	Fill Height	Gallons	Gal/Height	Gallons Delta
2	3''	22.3	7.4333
3	4''	31.0	7.7500	8.7
4	5''	39.7	7.9400	8.7
5	6''	48.4	8.0667	8.7
6	7''	57.2	8.1714	8.8
7	8''	65.9	8.2375	8.7
8	9''	74.6	8.2889	8.7
9	10''	83.4	8.3400	8.8
10	11''	92.1	8.3727	8.7
11	12''	100.8	8.4000	8.7
12	13''	109.6	8.4308	8.8
13	14''	118.3	8.4500	8.7
14	15''	127.0	8.4667	8.7
15	16''	135.7	8.4813	8.7
16	17''	144.5	8.5000	8.8
17	18''	152.2	8.4556	7.7
18	19''	160.9	8.4684	8.7
19	20''	169.6	8.4800	8.7
20	21''	177.6	8.4571	8.0
21	22''	186.4	8.4727	8.8
22	23''	195.1	8.4826	8.7
23	24''	203.8	8.4917	8.7
24	25''	212.5	8.5000	8.7
25	26''	221.3	8.5115	8.8
26	27''	230.0	8.5185	8.7
27	28''	238.7	8.5250	8.7
28	29''	247.5	8.5345	8.8
29	30''	263.0	8.7667	15.5
30	31''	271.7	8.7645	8.7
31	32''	280.4	8.7625	8.7
32	33''	289.2	8.7636	8.8
33	34''	297.9	8.7618	8.7
34	35''	306.6	8.7600	8.7
35	36''	316.9	8.8028	10.3
36	37''	325.6	8.8000	8.7
37	38''	334.3	8.7974	8.7
38	39''	343.1	8.7974	8.8
39	40''	351.8	8.7950	8.7
40	41''	360.5	8.7927	8.7
41	42''	370.2	8.8143	9.7
42	43''	379.0	8.8140	8.8
43	44''	387.7	8.8114	8.7
44	45''	396.4	8.8089	8.7
45	46''	405.2	8.8087	8.8
46	47''	413.9	8.8064	8.7
47	48''	422.6	8.8042	8.7
48	49''	431.3	8.8020	8.7
49	50''	440.1	8.8020	8.8
50	51''	448.8	8.8000	8.7
51	52''	457.5	8.7981	8.7
52	53''	466.3	8.7981	8.8
53	54''	477.6	8.8444	11.3
54	55''	486.3	8.8418	8.7
55	56''	495.1	8.8411	8.8
56	57''	503.8	8.8386	8.7
57	58''	512.5	8.8362	8.7
58	59''	521.2	8.8339	8.7
59	60''	530.0	8.8333	8.8
60	61''	538.7	8.8311	8.7
61	62''	547.4	8.8290	8.7
62	63''	556.2	8.8286	8.8
63	64''	564.9	8.8266	8.7
64	65''	573.6	8.8246	8.7
65	66''	582.4	8.8242	8.8
Sheet5

Cell Formulas
Range	Formula
A2:A65	A2	=SEQUENCE(66-2,,3)
C2:C65	C2	=ROUND(B2/A2,4)
D3:D65	D3	=B3-B2
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D65	Cell Value	<8.6	text	NO
D2:D65	Cell Value	>8.9	text	NO

The Custom Format for Column A is #"''" you can't have an odd number of double quotes in a custom format, so that's two single quotes between double quotes.
While the Gallons per inch seems to be fairly consistently between 8.7 and 8.8 gallons per inch, there are some HUGE discrepancies. I double checked the values in red and they're correct.
Unless you're talking about a container with varying widths, it makes no sense that one inch of water doesn't add a consistent number of gallons. You also don't explain what a "strapping report" is or what you're trying to calculate, but you'd better look into the data first!

 

[pujo]

Thanks @jdellasala

Pretty much the same issues I was having.
The only thing I can think of that would cause the discrepancy is possible tank bulge??

Yea, I should have put it in a table and use the BB code but I was in a hurry...
Appreciate your time!

PuJo

 

[AhoyNC]

Does your tote have rounded corners? If so, that could explain the difference.

 

[jdellasala]

Does your tote have rounded corners? If so, that could explain the difference.

One would have to imagine that a device built to handle 4,853.88 lbs. of water would have to be carefully manufactured, and so unlikely that the rounded corners would not be uniform and not explain why the table presented does not have a consistent number of gallons of water per inch, let alone huge variances.