  City-Data Forum Need Help Writing An Excel Formula
 User Name Remember Me Password [Register] Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
 View detailed profile (Advanced) or searchsite with Search Forums  (Advanced)

 5,217 posts, read 2,342,563 times Reputation: 14906

Quote:
 Originally Posted by gguerra I thought I already gave you a way. If it's not a formula, something you can calculate using MATH, you need to lookup a value in a table which you can provide the values for. The functions I suggested do JUST that. There is no other way. Do you expect to pull the number from thin air? If you knew the mathematical formula to calculate the inches per gallon you could easily do it in excel using a formula. Without that it's impossible. If you have a list of values you can use HLOOKUP and VLOOKUP. You don't have to be a programmer to figure those out.
And what are you supposed to do when the input value is in between two values in the table? Now you need to insert interpolation calculations, too.

After all, the table was simply calculated using the formula for the area of a partial circle cut off by a chord, knowing the height of the chord and the diameter of the circle, then multiplying by the known length of the tank. So why not look up the formula for the area and be done with it?  423 posts, read 89,610 times Reputation: 337
Quote:
 Originally Posted by Des-Lab Working on a project. putting together a spreadsheet to calculate fluid quantities in tanks/cylinders. All I want to do is plug in a number (inches) and have it convert it to gallons for me. I already have the charts. It's just a matter of writing it. Vertical tanks are easy. I can plug in a simple Sum formula to do the math, since the "gallons per inch" value is the same. So let's say the dimensions of the tank in question is 100 gallons per inch, I just type in X * 100 = and it's done. I write "17" in the cell and the multiplier kicks in and gives me "1700" in my "total" cell. Horizontal tanks though are another story. The inches per gallon value is variable due to the curvature of the tank; the quantity change is not a constant increment; the inches per gallon are not the same. Five inches might be 300 gallons, six inches 600 gallons, seven inches 1000 gallons, and so on. I have the conversion table here. But how can I write that as a formula to accomplish the same thing as with the vertical? "If X = X inches, X, =this many inches, and so on". But for my numerical value, I just enter the same number i.e. "12", and it picks up whatever quantity twelve inches happens to be. Am I making sense? How do I do this?
I understand. I work with excel all the time too, but have no idea how to do what you need. Unless you can look at the numbers a cook up an algebraic formula that approximates the conversion.  Location: McAllen, TX 3,570 posts, read 2,393,349 times Reputation: 4246
Quote:
 Originally Posted by turf3 And what are you supposed to do when the input value is in between two values in the table? Now you need to insert interpolation calculations, too. After all, the table was simply calculated using the formula for the area of a partial circle cut off by a chord, knowing the height of the chord and the diameter of the circle, then multiplying by the known length of the tank. So why not look up the formula for the area and be done with it?
If I understood the OP correctly, there would no values in between. If you can look it up on a printed table you can look it up in Excel. That is what the function does, it has to be listed. Otherwise, it is a formula that is calculated, something I thought the OP already understood since he's been using Excel for 20 years.

If the OP provided better examples we may understand the problem better.  5,217 posts, read 2,342,563 times Reputation: 14906
Quote:
 Originally Posted by gguerra If I understood the OP correctly, there would no values in between. If you can look it up on a printed table you can look it up in Excel. That is what the function does, it has to be listed. Otherwise, it is a formula that is calculated, something I thought the OP already understood since he's been using Excel for 20 years. If the OP provided better examples we may understand the problem better.
But there are always values in between. We're talking about the height of liquid in a tank. It's not some kind of Schrodinger's cat scenario where the liquid gets to 30 inches, then the liquid and tank don't exist until it reaches 31 inches. Furthermore, a linear interpolation between 30 and 31 isn't going to be accurate (though it might be good enough for the purpose), because volume as a function of height in a horizontal cylinder isn't a straight line but rather a curve. With all that trouble, why not just use the diameter and length of the tank, look up the formula for area of a partial circle (I found it in less than two minutes on Google), and multiply area x length to get volume? Seems to me like the fastest way to get there, rather than going 35 miles around the long way to get less useful results.  311 posts, read 309,222 times Reputation: 160
If you have the factor table why not perform a vlookup to retrieve the multiplier based on your inputted value?  Location: McAllen, TX 3,570 posts, read 2,393,349 times Reputation: 4246
Quote:
 Originally Posted by turf3 But there are always values in between. We're talking about the height of liquid in a tank. It's not some kind of Schrodinger's cat scenario where the liquid gets to 30 inches, then the liquid and tank don't exist until it reaches 31 inches. Furthermore, a linear interpolation between 30 and 31 isn't going to be accurate (though it might be good enough for the purpose), because volume as a function of height in a horizontal cylinder isn't a straight line but rather a curve. With all that trouble, why not just use the diameter and length of the tank, look up the formula for area of a partial circle (I found it in less than two minutes on Google), and multiply area x length to get volume? Seems to me like the fastest way to get there, rather than going 35 miles around the long way to get less useful results.
How is he supposed to get the height, not approximate but actual? With a measuring tape or ruler? Logic would tell me it's a rounded figure. If he has a conversion table, that can be reverse-engineered. You can get the formula from there given the right knowledge of course. I would assume some geometry, algebra etc. Also, if he has a conversion table, how does that handle in-between values?  5,217 posts, read 2,342,563 times Reputation: 14906
Yeah, sure, you can take a table of values and do a curve-fit approximation. There are multiple curve-fit types, so you have to calculate the variance at each tabulated point, and select the curve-fit type that yields the lowest total variance. Now since there's an arccosine in the actual calculation (oops, I let out a little piece of information), none of the standard curve-fits is going to yield particularly great results. Furthermore, there might be an erroneous point in the tabulated values. Now you have to decide for each point that doesn't lie close to the fitted curve, is it a possible data entry error, or is that because a polynomial (for example) can't exactly fit to a trig curve? Of course, you can decide to patch together multiple curves to get a fitted curve that reduces the total error, but now you're getting more complicated in your Expel shredsheet what with IF functions to evaluate which curve fit to use.

Anyone who's handled actual data can come up with all kinds of crazy stuff to try to do in very complicated ways, something that can be done by spending 5 minutes on Google and another 5 minutes to key in the formula to Excel (converting from cubic inches to gallons, or whatever mixed units are involved).

I wonder how the table was generated in the first place? HMMMMMMMM???  599 posts, read 181,864 times Reputation: 2235
Quote:
 Originally Posted by Des-Lab Working on a project. putting together a spreadsheet to calculate fluid quantities in tanks/cylinders. All I want to do is plug in a number (inches) and have it convert it to gallons for me. I already have the charts. It's just a matter of writing it. Vertical tanks are easy. I can plug in a simple Sum formula to do the math, since the "gallons per inch" value is the same. So let's say the dimensions of the tank in question is 100 gallons per inch, I just type in X * 100 = and it's done. I write "17" in the cell and the multiplier kicks in and gives me "1700" in my "total" cell. Horizontal tanks though are another story. The inches per gallon value is variable due to the curvature of the tank; the quantity change is not a constant increment; the inches per gallon are not the same. Five inches might be 300 gallons, six inches 600 gallons, seven inches 1000 gallons, and so on. I have the conversion table here. But how can I write that as a formula to accomplish the same thing as with the vertical? "If X = X inches, X, =this many inches, and so on". But for my numerical value, I just enter the same number i.e. "12", and it picks up whatever quantity twelve inches happens to be. Am I making sense? How do I do this?
I got through calculus II forty years ago, so my first inclination was that the answer is going to be a function calculation. But having worked the past 35 years in industry, I've gotten too lazy to figure this stuff out myself now, so I just Google it. I believe your answer can be found here:
https://www.mathopenref.com/cylindervolpartial.html

But I didn't study their answer, I want to try and resolve it myself, maybe tomorrow. Idle hands and the devil's workshop and all that, LOL. Please let me know if this does it for you.  28,243 posts, read 39,914,600 times Reputation: 36758
Your post is probably not going to be read by the OP. They got all upset over the lack of a concise answer and bailed. Apparently the suggestions given were not specific enough. i.e. They didn't hand the OP the answer so they bail because they didn't want to spend time understanding the obvious.  599 posts, read 181,864 times Reputation: 2235
Quote:
 Originally Posted by Tek_Freek Your post is probably not going to be read by the OP. They got all upset over the lack of a concise answer and bailed. Apparently the suggestions given were not specific enough. i.e. They didn't hand the OP the answer so they bail because they didn't want to spend time understanding the obvious.
You're probably correct, there goes two minutes of my life, sigh. I went back and re-read the answers others gave him and saw that he's not really looking for the formula required to do the calculation, he's trying to figure out how to express it in Excel, which is really a different question than the one I answered with the link anyway. I'm no Excel expert, but I've seen this done by others - I think it's a pretty standard macro he's looking to accomplish, probably available in an Excel tutorial on You Tube, I would think. The internet has really spoiled me to the point where I was able to retire without having to do my own cipherin' anymore, LOL. During my career, I had little practical use for calculus, quite a bit for algebra, and knowing trig was essential.

Kids, Math truly is Necessary, unless you want to spend your working years asking whether your customer wants fries with his order. You can look stuff up on the web, but unless you fully understand all the concepts, it's all Greek.  Please register to post and access all features of our very popular forum. It is free and quick. Over \$68,000 in prizes has already been given out to active posters on our forum. Additional giveaways are planned. Detailed information about all U.S. cities, counties, and zip codes on our site: City-data.com.  Quick Reply Message: City-Data Forum