City-Data Forum Need Help Writing An Excel Formula (convert, display, videos, Google)
 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.

07-09-2019, 09:48 AM
 Location: Business ethics is an oxymoron. 1,893 posts, read 2,283,111 times Reputation: 3997

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?

07-09-2019, 10:02 AM
 5,304 posts, read 2,369,292 times Reputation: 15081
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?
Go to the CRC Math Tables book, the section titled "Geometrical Mensuration" and you should be able to find the formula for volume of a partial cylinder based on its diameter, length, and the height of the partial cylinder.

You might have to find the formula for area of a partial circle and multiply it by the length.

07-09-2019, 10:12 AM
 Location: Business ethics is an oxymoron. 1,893 posts, read 2,283,111 times Reputation: 3997
No. I already have the chart that tells me what the inches per gallons are. My quandary is writing the correct formula in Excel so it knows what value to grab. For example, let's say that the chart says that ten inches equals 350 gallons, how do I write the formula to where I simply type in "10" and in the adjacent, "total" cell, it automatically writes "350"?

Again, for example....."If....

1 = 25
2 = 30
3 = 40
4 = 65

and so on and so on...

What do I write so that the first value auto populates the correct second one?

I know how to do this with vertical, as I explained in my OP. But the same methodology does not work with a horizontal.

07-09-2019, 10:32 AM
 Location: McAllen, TX 3,598 posts, read 2,408,694 times Reputation: 4276
Quote:
 Originally Posted by Des-Lab No. I already have the chart that tells me what the inches per gallons are. My quandary is writing the correct formula in Excel so it knows what value to grab. For example, let's say that the chart says that ten inches equals 350 gallons, how do I write the formula to where I simply type in "10" and in the adjacent, "total" cell, it automatically writes "350"? Again, for example....."If.... 1 = 25 2 = 30 3 = 40 4 = 65 and so on and so on... What do I write so that the first value auto populates the correct second one? I know how to do this with vertical, as I explained in my OP. But the same methodology does not work with a horizontal.
How do you get 350 from 10? Is it a formula or is it based on a table of values?

If it's the latter, you could use IF but that would get long and is limited in length, it depends on how many different values.

Other functions that might work are HLOOKUP or VLOOKUP, I'm not sure if these are available in older versions of Excel, I use 2016. There are other "Lookup and Reference" functions that are similar.

What version of Excel are you using?

07-09-2019, 10:41 AM
 Location: Business ethics is an oxymoron. 1,893 posts, read 2,283,111 times Reputation: 3997
Using Excel 2016. So the "IF" would be an acceptable alternative since it doesn't really need to do any math. I would have to write in the entire table, but once done, should work.

To simplify, it would need to be something like this, using two columns. One being the value I type in. And the second to auto populate.

1=10
2=15
3=20
4=30
5=40
6=60
7=100
8=150
9=180
10=200

I realize there's no pattern to those numbers. I just picked them for illustration. If a value of "5" is equal to "40", I want to be able to to type "5" in one cell and have another cell automatically fill in with "40".

07-09-2019, 10:55 AM
 Location: McAllen, TX 3,598 posts, read 2,408,694 times Reputation: 4276
Yes, as stated the HLOOKUP or VLOOKUP functions may work. You would still have to write an entire table since there is no calculation you can use to come up with the values, right?

This is on the HLOOKUP function, the VLOOKUP functions works the same way except it's for a vertical table.
https://exceljet.net/excel-functions...ookup-function

There are also youtube videos on it which came up when I did a google search.

07-09-2019, 11:21 AM
 5,304 posts, read 2,369,292 times Reputation: 15081
Sheesh, just calculate the area of a partial circle of the diameter of your tank, cut off by a chord at the height of your liquid level, multiply it by the length of the tank.

It's a one line formula in Expel. I am not going to look it up for you.

07-09-2019, 11:25 AM
 Location: McAllen, TX 3,598 posts, read 2,408,694 times Reputation: 4276
Quote:
 Originally Posted by turf3 Sheesh, just calculate the area of a partial circle of the diameter of your tank, cut off by a chord at the height of your liquid level, multiply it by the length of the tank. It's a one line formula in Expel. I am not going to look it up for you.
He obviously doesn't know the formula.

07-09-2019, 11:38 AM
 Location: Business ethics is an oxymoron. 1,893 posts, read 2,283,111 times Reputation: 3997
Mods: feel free to close or delete this. I came here looking for help. What has me confused might be second nature to others. I thought I explained it as clearly and succinctly as I could. Sheesh this isn't even a political discussion and still some people have to come and take a dump on it.

I guess I'll try Google again. So far everything I've found is as clear as mud. I've been using Excel for 20 years so it's not like I'm a novice at it. I've just never done this particular function.

I'll ask one last time: How do I program it to where if I type X in one cell, it will automatically display "Y" in another?

That's the simplest way I can explain it. I already have all the numbers and the tables and I don't need to dust off any of my math books from college.

07-09-2019, 01:08 PM
 Location: McAllen, TX 3,598 posts, read 2,408,694 times Reputation: 4276
Quote:
 Originally Posted by Des-Lab Mods: feel free to close or delete this. I came here looking for help. What has me confused might be second nature to others. I thought I explained it as clearly and succinctly as I could. Sheesh this isn't even a political discussion and still some people have to come and take a dump on it. I guess I'll try Google again. So far everything I've found is as clear as mud. I've been using Excel for 20 years so it's not like I'm a novice at it. I've just never done this particular function. I'll ask one last time: How do I program it to where if I type X in one cell, it will automatically display "Y" in another? That's the simplest way I can explain it. I already have all the numbers and the tables and I don't need to dust off any of my math books from college.
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.

Edit: I read your post again. If I am understanding you correctly do just need the syntax and not the actual formula? It doesn't make sense for you to ask that if you have been using Excel for 20 years.
 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.