# 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