You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Tank Formula with different increment factors

Advertisement

QUESTION: Hi, I need a way to write a formula that i know is way above my head. i have a vertical tank with 5 different sections. from 0 ft. to 6 ft. 1/2 inch, there are 290 increments of .69724 in the volume. the other sections have their own measurements with the amount of increments and increment factor. i want to write a function for the first section and hopefully be able to understand how it works and apply it to the rest. i want to be able to put the level in ft. and inches and subtract bottom gauge from top gauge to give you amount in volume. thanks

ANSWER: First work backwards:

Convert to inches

Inches 6x12+.5 72.5 inches

290 increments

290/72.5 4 increments per inch

4*.69724 2.78896 volume per inch

Enter your measurements in inches and multiply by volume

2'6"" 30 inches

30*2.78896 83.6688 volume

A1 = 72.5 (Max inches in container)

B1 = 290 (increments in container)

C1 = .69724 (volume per increment)

D1 = measurement taken in inches only

E1 formula would be:

=B1/A1*C1*D1

---------- FOLLOW-UP ----------

QUESTION: Nice, how would i write a formula in which if the measuremt will determine the increment. For example, if the measurement falls below 6.2 feet then its x formula with its own increment per volume. If its above 6.2 but below 13.3 then its x formula with its own increment per volume. There are 5 different sections to the tank and i am trying to make a calculator with excel without having to take out strapping chart for each section. Thanks.

You nest the 5 section formulas inside each other starting at the HIGHEST section of the tank.

In INCHES only:

Top = 160 inches+

2nd Starts at 120 inches

3rd starts at 80 inches

4th starts at 40 inches

5th is the remaining 0+ inches

=IF(D1>160, (First Formula), IF(D1>120, (Second Formula), IF(D1>80, (Third Formula), IF(D1>80, (Fourth Formula), (Fifth Formula)))))

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files
=====================
I have been offering free assistance as an Excel aid on many web sites for many years:
(http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)**Education/Credentials**

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition**Awards and Honors**

Microsoft Excel MVP 2010