Wikipedia:Reference desk/Archives/Mathematics/2016 May 6

Mathematics desk
< May 5	<< Apr | May | Jun >>	May 7 >

Welcome to the Wikipedia Mathematics Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

May 6

What kind of function does this data represent? How do I represent the formula in Excel?

I have a set of data,and I typed it into an Excel spreadsheet. See below.

Inches		Gallons		Difference		Average
1		3		3		3
2		8		5		4
3		14		6		5
4		22		8		6
5		31		9		6
6		40		9		7
7		50		10		7
8		61		11		8
9		73		12		8
10		85		12		9
11		97		12		9
12		110		13		9
13		123		13		9
14		136		13		10
15		150		14		10
16		164		14		10
17		178		14		10
18		193		15		11
19		207		14		11
20		222		15		11
21		237		15		11
22		252		15		11
23		267		15		12
24		282		15		12
25		297		15		12
26		312		15		12
27		327		15		12
28		348		21		12
29		356		8		12
30		371		15		12
31		386		15		12
32		400		14		13
33		414		14		13
34		427		13		13
35		441		14		13
36		454		13		13
37		467		13		13
38		479		12		13
39		491		12		13
40		502		11		13
41		514		12		13
42		524		10		12
43		533		9		12
44		542		9		12
45		549		7		12
46		556		7		12
47		561		5		12
48		564		3		12

The table represents some data that came with my oil furnace. The first column reflects how many inches of oil are in the furnace. The second column converts the inches into how many gallons of oil are present in the furnace. I myself created the third and fourth columns. So, my question is: why would the relationship between inches and gallons be so odd? I was expecting a simple linear function. Why would it not be linear? What would be the correct name for this function? How can I determine the equation (function) with which I can input "inches" and determine "gallons"? Thanks. Joseph A. Spadaro (talk) 02:20, 6 May 2016 (UTC)

Are you sure about the line (28", 348 gal)? That value looks out of place. I'd have expected (28", 342 gal) instead. -- ToE 03:11, 6 May 2016 (UTC)

Thanks. Yes, I double checked the "28 inches = 348 gallons" line. It was actually the one below that that raised my eyebrows the most (the "29 inches = 356 gallons" line). These numbers make no sense. I don't see a rhyme or reason. But, then again, I was expecting a purely simple linear function. Joseph A. Spadaro (talk) 03:18, 6 May 2016 (UTC)

Then that line (28", 348 gal) is most likely in error in your furnace's documentation. Perhaps whoever typed up the table for the furnace or oil tank manufacturing company misread a "342" as a "348" from the list of values handwritten by whoever measured the actual capacity of that model of tank. Note that if it were (28", 342 gal), then the differences on the the (29", 356 gal) would be repaired as well. -- ToE 03:33, 6 May 2016 (UTC)

OK. Let's assume that was a typo in the documentation. Still, the rest of the numbers seem odd to me. I guess I see a pattern, "sort of". But not really. Joseph A. Spadaro (talk) 04:07, 6 May 2016 (UTC)

Calculating a "first difference" column was a smart move. Notice that when you are more than a foot and a half from either end of the tank, the volume increases at a rate of 14 to 15 gallons per inch, meaning that the cross sectional area of the internal volume of the tank is approximately 15 gal ÷ 1 inch = 15 · 231 cubic inches ÷ 1 inch = 3465 square inches. (Assuming the US liquid gallon.) If you measure the length of the tank and multiply it by its maximum width, you should get a number close to but a bit larger than this, as the external dimensions also include the thickness of the tank walls. (Internal structures such as baffles or pipes will also throw the numbers off a bit.)

Also notice that the difference column is (to within one gallon) symmetric top to bottom, meaning that the top shape of the tank is the same as the bottom shape of the tank. -- ToE 07:05, 6 May 2016 (UTC)

Yes, I did notice that the "difference column" (column 3) had numbers that appear to be in a (semi-)circular or "bow" pattern. Joseph A. Spadaro (talk) 07:16, 6 May 2016 (UTC)

Is the oil tank rounded at the top and bottom, as with this typical tank? -- ToE 03:35, 6 May 2016 (UTC)

Not sure. Have to double check. But I think so. Why? Joseph A. Spadaro (talk) 04:05, 6 May 2016 (UTC)

The relation is linear only if the horizontal cross sectional area of the tank is constant with height. See Cavalieri's principle.--Jasper Deng (talk) 05:12, 6 May 2016 (UTC)

So, with the numbers above, that clearly cannot be the case. I assume. What would be the correct name for this function? How can I determine the equation (function) with which I can input "inches" and determine "gallons"? I want to create a modified version of that chart. In my modified version, I want the inches (first column) to increase by 1/4 inch each time (as opposed to the current increase of 1 full inch). So, I'd want the first column to be 1 inch; 1.25 inch; 1.50 inch; 1.75 inch; 2.00 inch; and so on. With the correct corresponding gallon values. Thanks. Joseph A. Spadaro (talk) 05:41, 6 May 2016 (UTC)

You need to know the horizontal cross-sectional area of the tank as a function of height for an exact answer. Your problem is one of interpolation, for which there are many possible approaches. For now all I can say is that the volume is an increasing function of the water level.--Jasper Deng (talk) 05:57, 6 May 2016 (UTC)

If you really wanted to, you should be able to generate a three part piecewise function to model the volume as a function of fill height. (And if I were doing so, I would first see how well a semicircular top and bottom of the ends worked, and if they matched closely, then I'd have a good prediction of the overall dimensions of your tank just from your table.) But that doesn't seem necessary if you just want to increase the resolution of your table to the quarter inch. Linear interpolation should work well enough -- even for the top or bottom inch (where the true function will be the least linear) as your volumes were given only to the nearest whole gallon anyway. -- ToE 07:25, 6 May 2016 (UTC)

I was thinking of doing some type of interpolation. And I was thinking of this: I would take all of the gallon values between, say, inch 12 and inch 13. So, 12 inches equals 110 gallons; 13 inches equals 123 gallons. That is a difference of 1 inch and its corresponding 13 gallons. So, for each half-inch, there is an approximate increase of 6.5 gallons. And for each quarter-inch, there is an approximate increase of 3.25 gallons. Is that a correct interpolation? I don't need the numbers to be 100% precise and exact. A decent "ball park" figure is fine. I assume my interpolation method is "accurate enough" (for my purposes). But, here is my problem, since this function is not linear. That specific interpolation will only work in that specific interval of inches (12 to 13 inches). It has nothing to do with the other intervals in the chart. Do I have to create an interpolation 48 separate times? One interpolation that occurs between inch #12 and inch #13. (Which I did above as my example.) Then a totally different interpolation for inch #13 and inch #14? And so on, for all 48 inches? Is that what I need to do? There is no "one" model that will work for all 48 inch intervals? Thanks. Joseph A. Spadaro (talk) 15:44, 6 May 2016 (UTC)

Here is a quick and dirty fit using a logistic function as a base:

${\text{Gallons}}={786.38 \over 1+e^{0.0785*\left({\text{inches}}-23.834\right)}}-113.31$

Compared to your table the average error is about +/- 2 gallons, with the max error of a little more than 6 gallons. Perhaps not ideal, but you didn't specify how accurate a solution you wanted, and of course increasing accuracy will presumably mean more complicated expressions. Dragons flight (talk) 07:53, 6 May 2016 (UTC)

Thanks. But I am confused. Shouldn't there be an exact and precise function for something like this? I mean, the shape of the tank is not random. It is indeed some very specific geometric shape. Joseph A. Spadaro (talk) 15:37, 6 May 2016 (UTC)

Your linear interpolation is fine between about fourteen inches and 36 inches. Outside this range, I would just draw a graph and sketch the smoothest curve going near to the points, and use this for interpolation. Remember that the given data has been rounded to a whole number of gallons, so your interpolation could be more accurate than the printed values. The tank might not be an exact geometric shape, and internal structure might distort any data derived from a function. Dbfirs 15:56, 6 May 2016 (UTC)

You are saying that my linear interpolation is fine (between a certain section of the chart). So, I have to essentially do 48 separate interpolations? Joseph A. Spadaro (talk) 16:08, 6 May 2016 (UTC)

No, I'm saying that the graph is a straight line between these limits because the cylinder has a constant cross-section, so you can use a very simple formula using equal increments. My claim is that this formula might be more accurate than the data published, because of rounding. Dbfirs 08:41, 7 May 2016 (UTC)

@Joseph A. Spadaro: ${\frac {\Delta h}{\Delta v}}$ against h looks a lot like a semi ellipse to me, which would make sense if your tank is a cylinder with a horizontal axis (imperial units (I think) make it an ellipse rather than a circle). If you take that to be true, then ${\frac {dh}{dv}}={\sqrt {\frac {900(49x-x^{2})}{2401}}}$ (based upon the vertices being (0,0), (49,0) and (24.5,15)). This gives the rather untidy equation for v in terms of h as ${\sqrt {\frac {900}{2401}}}({\frac {x^{\frac {3}{2}}}{2}}{\sqrt {49-x}}-12.25{\sqrt {x(49-x)}}+600.25\arcsin {\frac {\sqrt {x}}{7}})$. I will admit I used wolfram alpha for the integral and an automated tool for the ellipse. I'm lazy. —  crh ₂₃  (Talk) 17:45, 6 May 2016 (UTC)

@Joseph A. Spadaro: No. There are infinitely many functions that interpolate your data. So unless you know more about the shape of the tank, no function will necessarily be better than another. The reason is that if you give me any function that interpolates the data, I can add to it any other function that is zero at the integers and obtain another valid interpolating function.--Jasper Deng (talk) 18:01, 6 May 2016 (UTC)

Your tank is a simple cylinder with a radius of $r\approx 24.01$ inches. If h is the height in inches:

${\text{Gallon}}=14.9630*{1 \over 2}\left(\left(h-r\right){\sqrt {1-\left({h-r \over r}\right)^{2}}}+r\sin ^{-1}\left({h-r \over r}\right)+{\pi r \over 2}\right)$

Except for the h = 28 value that you already noted appears weird, this formula agrees with your table for all values within less than 1 gallon. Dragons flight (talk) 19:15, 6 May 2016 (UTC)

@Dragons flight: Thanks. But, are you sure that formula works correctly? When I apply that formula, I get different results. For example, when I input 8 inches, I get a value of negative 502.5414 gallons. (When it should be positive 61 or so.) And other input numbers for other inch values also yield incorrect results for gallons. See my related question, posted here: Wikipedia:Reference desk/Computing#How do I translate this equation into an Excel formula?. Thanks. Joseph A. Spadaro (talk) 04:51, 7 May 2016 (UTC)

I made a sign error with the last term, now fixed. Sorry about that. Dragons flight (talk) 07:51, 7 May 2016 (UTC)

@Dragons flight: Are you sure that you are not also missing another factor of r? I assume you rolled it into the constant (14.9630), but without units I can't be sure.--Jasper Deng (talk) 08:11, 7 May 2016 (UTC)

Holy smoke. How on earth did you come up with that formula? (Generally, speaking. I don't need/want all the specifics.) So, that formula will work for any value that I plug in for "inches"? Even if the inches are 1/4-inch or 1/2-inch increments? Thanks. Joseph A. Spadaro (talk) 20:21, 6 May 2016 (UTC)

Also, do you have the Excel version of how that formula is typed? Thanks. Joseph A. Spadaro (talk) 21:06, 6 May 2016 (UTC)

(edit conflict) He made an educated guess about the shape of your tank, namely that it is a cylinder lying on its side with radius r. Then the cross-sectional area at height h is given by $A(h)=2l{\sqrt {r^{2}-(h-r)^{2}}}$ where l is the length of the cylinder. The volume contained below height h is given by integration (roughly speaking, summing up all the little slices below height h, each of which has area given by the cross-section and thickness dh), i.e. $V(h)=\int _{0}^{h}A(t)dt=2l\int _{0}^{h}{\sqrt {r^{2}-(t-r)^{2}}}dt$. Using a trigonometric substitution to evaluate the integral should yield what Dragon's flight has above. --Jasper Deng (talk) 21:08, 6 May 2016 (UTC)

Addendum: For Excel, use this. I also strongly encourage you to empirically verify the correctness of this formula because it is based on the assumption that the tank forms a perfect cylinder lying perfectly on its side. For posterity I also encourage you to learn the mathematical derivation of formulae like this so you can solve similar problems on your own.--Jasper Deng (talk) 21:10, 6 May 2016 (UTC)

You stated: For posterity I also encourage you to learn the mathematical derivation of formulae like this so you can solve similar problems on your own. No, I don't understand any of this. It's all over my head. Thanks. Joseph A. Spadaro (talk) 21:15, 6 May 2016 (UTC)

@Joseph A. Spadaro: Well, you asked for how the formula was derived. You won't be able to do that (not even at a basic level) without at least an intuitive understanding of integral calculus. Think about slicing the tank into infinitely many infinitesimally thin sheets of volume and adding up the volumes of all of them. Note that when $h=2r$ the formula reduces to just the volume of a cylinder.

Not to be nitpicky, but it is also wrong to assume that we could have found the formula just from the data points, because like I said, there are many ways to interpolate a given set of data. The formula here was based on what turned out to be quite a good guess at the shape of the tank, which is more information than just the data points alone.--Jasper Deng (talk) 21:34, 6 May 2016 (UTC)

I love calc as much as the next bloke, but a bit of trig will do this job quicker. Our article Circular segment unfortunately does not include area as a function of sagitta h, but it does include the easily derived area as a function of central angle θ. A=(r²/2)(θ-sinθ). It also includes the as easily derived h=r[1-cos(θ/2)]. Solve for θ, substitute into the formula for A, apply a trig identity or two, and Bob's your uncle: A=r²acos(1-h/r)-(r-h)sqrt(2rh-h²). -- ToE 04:29, 7 May 2016 (UTC)

@Thinking of England: My calculus-related comment was meant for a general tank where you do not necessarily have the nice properties of a cylinder, which I had to assume because the OP had not confirmed that it was indeed a cylinder.--Jasper Deng (talk) 08:03, 7 May 2016 (UTC)

I don't understand that formula. It is giving me "A", which is "area"? The area of what? I want a formula where I can enter "H" (height in inches) and get a result of "G" (gallons). Joseph A. Spadaro (talk) 05:03, 7 May 2016 (UTC)

@Joseph A. Spadaro: Just multiply by the length of the tank, which for a cylindrical tank is constant with respect to the height. The area given here is the vertical cross-sectional area (distinct from the horizontal cross-sectional area I used above).--Jasper Deng (talk) 08:13, 7 May 2016 (UTC)

Thanks. Does anyone know the exact formula that one would type into Excel to match the formula above by User Dragons flight? This link doesn't make any sense to me at all. Thanks. Joseph A. Spadaro (talk) 21:14, 6 May 2016 (UTC)

Taking a step back from the above discussions, I roughly plotted the "difference" data above, and they appear to be a good fit for a horizontal cylinder with a radius of 24 inches and a length of 72 inches. This gives us a volume of about 130,000 cubic inches which equates to about 564 gallons. A web search yielded several tank volume calculations, including this one, which will calculate the liquid volume from the tank dimensions (Length = 72, Diameter = 2 x Radius = 48 inches) and fill depth. A spot check of the results shows a very good match. As for the formula, this page is among several that give variants of what I've adapted as:

${\text{Gallon}}={1 \over 231}L\left(R^{2}\cos ^{-1}\left({R-h \over R}\right)-\left(R-h\right){\sqrt {2Rh-h^{2}}}\right)$

where R = radius = 24 inches, L =length = 72 inches, and h is the measured fill depth. The first part of the inner calculation calculates the "pi slice" sector cross-sectional area that includes the measured liquid, and the second part subtracts out the triangular portion above the liquid, leaving only the liquid cross section. Multiplying by cylinder length and converting to gallons yields the desired result. The first of the above referenced web pages has a good illustration of this. An equivalent Excel formula would be:

=(1/231)*A2*(A1*A1*ACOS((A1-A3)/A1)-(A1-A3)*SQRT(2*A1*A3-A3*A3))

where A1 is R, A2 is L, and A3 is h. -- Tom N talk/contrib 07:50, 7 May 2016 (UTC)

I found the glitch in Dragon Flight's equation - the sin^-1 term should be subtracted, not added:

${\text{Gallon}}=\color {Red}14.9630*{1 \over 2}\left(\left(h-r\right){\sqrt {1-\left({h-r \over r}\right)^{2}}}{\color {Red}{-}}r\sin ^{-1}\left({h-r \over r}\right)+{\pi r \over 2}\right)$, ---(Update: This was wrong. Dragon Flight's equation above is correct.)

or in Excel:

=14.963*(1/2)*((A12-A1)*SQRT(1-((A12-A1)/A1)^2)-(A1*ASIN((A12-A1)/A1))-((PI()*A1)/2)) (as requested on computing board)

=14.963*(1/2)*((A12-A1)*SQRT(1-((A12-A1)/A1)^2)+(A1*ASIN((A12-A1)/A1))+((PI()*A1)/2)) (corrected)

It wasn't obvious at first,but Dragon Flight's corrected equation and the one I posted just above are equivalent.

${\begin{aligned}{\text{Gallon}}&={1 \over 231}L\left(R^{2}\cos ^{-1}\left({R-h \over R}\right)-\left(R-h\right){\sqrt {2Rh-h^{2}}}\right)\\&={1 \over 231}L\left(R^{2}\left({\pi \over 2}{\color {Red}{-}}\sin ^{-1}\left({R-h \over R}\right)\right)-\left(R-h\right){\sqrt {R^{2}-(R-h)^{2}}}\right){\color {Red}{\text{corrected}}}\\&={1 \over 231}L\left({\pi R^{2} \over 2}{\color {Red}{-}}R^{2}\sin ^{-1}\left({R-h \over R}\right)-\left(R-h\right)R{\sqrt {1-\left({R-h \over R}\right)^{2}}}\right){\color {Red}{\text{corrected}}}\\&={1 \over 231}LR\left({\pi R \over 2}{\color {Red}{+}}R\sin ^{-1}\left({h-R \over R}\right)+\left(h-R\right){\sqrt {1-\left({h-R \over R}\right)^{2}}}\right){\color {Red}{\text{corrected}}}\\&=7.48\left(\left(h-R\right){\sqrt {1-\left({h-R \over R}\right)^{2}}}{\color {Red}{+}}R\sin ^{-1}\left({h-R \over R}\right)+{\pi R \over 2}\right){\color {Red}{\text{corrected}}}\\\end{aligned}}$

-- Tom N talk/contrib 02:23, 8 May 2016 (UTC)

My apologies to Dragon Flight and any who tried to make sense of my last post. Dragon Flight had already corrected his equation, but I was working off an old version when I tried to reconcile the differences and ended up introducing my own error (another flipped sign). The corrections have been annotated above. -- Tom N talk/contrib 00:14, 11 May 2016 (UTC)

Polynomial interpolation

Let x be the number of inches and y=f₁(x) be the number of gallons.

f₁(24)=282, f₁(36)=454, f₁(48)=564.

The table shows that f₁ is symmetric around the point (x,y)=(24,282).

f₂(x)=f₁(24+x)−282 is an odd function. f₂(x)=−f₂(−x).

f₂(0)=0, f₂(12)=172, f₂(24)=282.

f₃(x)=f₂(x)−282x/24 =f₁(24+x)−282−47x/4

f₃(0)=0, f₃(12)=31, f₃(24)=0.

f₄(x)=(1−(x/24)²)x/24

f₄(0)=0, f₄(12)=3/8, f₄(24)=0.

f₅(x)=31*8/3 f₄(x)=31(1−(x/24)²)x/9

f₅(0)=0, f₅(12)=31, f₅(24)=0,

f₅(x) interpolates f₃(x).

f₆(x)=f₅(x)+47x/4 =31(1−(x/24)²)x/9+47x/4 interpolates f₂(x)

f₇(24+x)=282+(31(1−(x/24)²)/9+47/4)x interpolates f₁(24+x).

This third degree polynomial reproduces the table within a few gallons.

  0   0
  1   5
  2  11
  3  18
  4  26
  5  34
  6  43
  7  53
  8  63
  9  74
 10  86
 11  98
 12 110
 13 123
 14 136
 15 150
 16 164
 17 178
 18 192
 19 207
 20 222
 21 237
 22 252
 23 267
 24 282
 25 297
 26 312
 27 327
 28 342
 29 357
 30 372
 31 386
 32 400
 33 414
 34 428
 35 441
 36 454
 37 466
 38 478
 39 490
 40 501
 41 511
 42 521
 43 530
 44 538
 45 546
 46 553
 47 559
 48 564

Bo Jacoby (talk) 20:28, 11 May 2016 (UTC).

Thanks, all. Joseph A. Spadaro (talk) 03:20, 12 May 2016 (UTC)

That interpolation is clearly not as good as the ones previously given, which match even more closely and are derived from the (assumed) geometry of the tank.--Jasper Deng (talk) 06:39, 12 May 2016 (UTC)

Yes sir! But your method is like shooting sparrows with a cannon. My derivation is simplified like this. f(0)=0, f(24)=282, f(48)=564 says that f(x)=564x/48 for x=0, x= 24 and x=48. So f(x)≅x(564/48+A(x−24)(x−48)). Solving f(12)=110 for A gives the simple formula y≅(11.75−0.006(24−x)(48−x))x for 0≤x≤48. Bo Jacoby (talk) 07:03, 12 May 2016 (UTC).

How did you decide how to choose those points to avoid Runge's phenomenon?--Jasper Deng (talk) 19:56, 12 May 2016 (UTC)

The points (0,0) and (48,564) and (24,282) are chosen because we had better reproduce the empty tank and the full tank and the half full tank exactly. (12,110) or (36,454) defines the deviation from linearity. The Runge phenomenon occurs for polynomials of degree higher than 3. Bo Jacoby (talk) 21:48, 12 May 2016 (UTC).