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)