May 6, 2016 - Wikipedia Math Reference Desk



https://en.wikipedia.org/wiki/Wikipedia:Reference_desk/Archives/Mathematics/2016_May_6


May 6

edit

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

edit

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)Reply

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)Reply
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)Reply
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)Reply
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)Reply
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)Reply
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)Reply
Is the oil tank rounded at the top and bottom, as with this typical tank? -- ToE 03:35, 6 May 2016 (UTC)Reply
Not sure. Have to double check. But I think so. Why? Joseph A. Spadaro (talk) 04:05, 6 May 2016 (UTC)Reply
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)Reply
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)Reply
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)Reply
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)Reply
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)Reply
Here is a quick and dirty fit using a logistic function as a base:
 
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)Reply
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)Reply
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)Reply
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)Reply
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)Reply
@Joseph A. Spadaro:   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   (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  . I will admit I used wolfram alpha for the integral and an automated tool for the ellipse. I'm lazy. —  crh 23  (Talk) 17:45, 6 May 2016 (UTC)Reply
@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)Reply

Your tank is a simple cylinder with a radius of   inches. If h is the height in inches:

 

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)Reply

@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)Reply
I made a sign error with the last term, now fixed. Sorry about that. Dragons flight (talk) 07:51, 7 May 2016 (UTC)Reply
@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)Reply
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)Reply
Also, do you have the Excel version of how that formula is typed? Thanks. Joseph A. Spadaro (talk) 21:06, 6 May 2016 (UTC)Reply
(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   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.  . 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)Reply
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)Reply
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)Reply
@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   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)Reply
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=(r2/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=r2acos(1-h/r)-(r-h)sqrt(2rh-h2). -- ToE 04:29, 7 May 2016 (UTC)Reply
@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)Reply
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)Reply
@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)Reply
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)Reply
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:
 
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)Reply
I found the glitch in Dragon Flight's equation - the sin-1 term should be subtracted, not added:
 , ---(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.
 
-- Tom N talk/contrib 02:23, 8 May 2016 (UTC)Reply
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)Reply

Polynomial interpolation

edit

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

f1(24)=282, f1(36)=454, f1(48)=564.

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

f2(x)=f1(24+x)−282 is an odd function. f2(x)=−f2(−x).

f2(0)=0, f2(12)=172, f2(24)=282.

f3(x)=f2(x)−282x/24 =f1(24+x)−282−47x/4

f3(0)=0, f3(12)=31, f3(24)=0.

f4(x)=(1−(x/24)2)x/24

f4(0)=0, f4(12)=3/8, f4(24)=0.

f5(x)=31*8/3 f4(x)=31(1−(x/24)2)x/9

f5(0)=0, f5(12)=31, f5(24)=0,

f5(x) interpolates f3(x).

f6(x)=f5(x)+47x/4 =31(1−(x/24)2)x/9+47x/4 interpolates f2(x)

f7(24+x)=282+(31(1−(x/24)2)/9+47/4)x interpolates f1(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).Reply

Thanks, all. Joseph A. Spadaro (talk) 03:20, 12 May 2016 (UTC)Reply
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)Reply

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).Reply

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

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).Reply