07092019, 09:48 AM



Location: Business ethics is an oxymoron.
1,882 posts, read 2,276,618 times
Reputation: 3973


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?

07092019, 10:02 AM



5,207 posts, read 2,342,563 times
Reputation: 14896


Quote:
Originally Posted by DesLab
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.

07092019, 10:12 AM



Location: Business ethics is an oxymoron.
1,882 posts, read 2,276,618 times
Reputation: 3973


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.

07092019, 10:32 AM



Location: McAllen, TX
3,567 posts, read 2,393,349 times
Reputation: 4241


Quote:
Originally Posted by DesLab
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?

07092019, 10:41 AM



Location: Business ethics is an oxymoron.
1,882 posts, read 2,276,618 times
Reputation: 3973


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".

07092019, 10:55 AM



Location: McAllen, TX
3,567 posts, read 2,393,349 times
Reputation: 4241


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/excelfunctions...ookupfunction
There are also youtube videos on it which came up when I did a google search.

07092019, 11:21 AM



5,207 posts, read 2,342,563 times
Reputation: 14896


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.

07092019, 11:25 AM



Location: McAllen, TX
3,567 posts, read 2,393,349 times
Reputation: 4241


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.

07092019, 11:38 AM



Location: Business ethics is an oxymoron.
1,882 posts, read 2,276,618 times
Reputation: 3973


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.

07092019, 01:08 PM



Location: McAllen, TX
3,567 posts, read 2,393,349 times
Reputation: 4241


Quote:
Originally Posted by DesLab
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: Citydata.com.

