formulas for volume and/or area
Posted by Lisa on January 02, 2002 3:37 PM
Is there a formula to calculate length*width*height?
I know I can use SUM to calculate across 3 columns, but I'd like to keep it in one. Will it work with numbers like 2'3" * 7'8" * 4' 5"?
Posted by Russell Hauf on January 02, 2002 5:27 PM
Well, this is one thing that is probably done better in VBA. Not that you can't do it with formulas (I'm sure you'll get formula responses to this). First off, I'd like to say that if there is any way you could store your mesaurements as inches, that would be ideal. If not, then try the following code. To get the Area (length * width * height), just call the function from a cell in your workbook like this:
AreaVol(A4,B4,C4), where your length, width and height are in A4, B4, and C4 (it does not matter if you have them in the right order since we are taking the product of the 3). I'm sure that you'll be able to see how you could modify this for volume of a square as well.
Hope this helps,
Public Function AreaVol(Length As Range, Width As Range, Height As Range) _
Dim lngInches As Long
lngInches = GetInches(Length.Value) * GetInches(Width.Value)
lngInches = lngInches * GetInches(Height.Value)
AreaVol = FeetAndInches(lngInches)
Public Function GetInches(SomeLength As String) As Integer
Dim FeetSeparator As Integer
FeetSeparator = InStr(SomeLength, "'")
GetInches = Left(SomeLength, FeetSeparator - 1) * 12 + _
Mid(SomeLength, FeetSeparator + 1, _
Len(SomeLength) - FeetSeparator - 1)
Public Function FeetAndInches(Inches As Long) As String
FeetAndInches = Inches \ 12 & "'" & Inches Mod 12 & Chr(34)
Posted by Russell Hauf on January 02, 2002 5:35 PM
Please note that there is no error checking in these formulas. It assumes all of the measurements are entered correctly, with the single and double quotes in the right places. If you are going to use this workbook extensively, I suggest that you build in some sort of error checking (let me know if you need some help).
-rh lngInches = GetInches(Length.Value) * GetInches(Width.Value) lngInches = lngInches * GetInches(Height.Value) AreaVol = FeetAndInches(lngInches)