You are here:

Excel/Tank Formula with different increment factors

Advertisement


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

Answer
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)))))
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

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

Experience

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

©2016 About.com. All rights reserved.