MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Thanks,
Lisa


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,

Russell

(code follows)


Option Explicit
Public Function AreaVol(Length As Range, Width As Range, Height As Range) _
As String

Application.Volatile
Dim lngInches As Long

lngInches = GetInches(Length.Value) * GetInches(Width.Value)
lngInches = lngInches * GetInches(Height.Value)

AreaVol = FeetAndInches(lngInches)

End Function
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)


End Function


Public Function FeetAndInches(Inches As Long) As String

FeetAndInches = Inches \ 12 & "'" & Inches Mod 12 & Chr(34)

End Function

Posted by Russell Hauf on January 02, 2002 5:35 PM

PLEASE NOTE!

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)