Formula(s) for area with more than four sides

B

BradHaberman

I'm new to this type of thing (posting messages). I'm trying to create
formula(s) that will give me the area of a home with many exterio
sides. The worksheet will allow for the direction of the wall; up
down, left or right, and the length of each wall.

The shape I am using goes something like this: Up 3, Right 1, Up 2
Right 2, Up 1, Right 2, Down 2, Right 2, Down 4, Left 2, Up 1 , Left 1
Up 1, Left 1, Down 1, Left 1, Down 1, and Left 2.

I know it is a complex shape, but all angles are right angles, so i
shouldn't be impossible, not that anything is impossible. Common sens
says just to do it my head and write it down, but the program is for m
neighbor, and I want to make it as simple as possible.

Thanks Bra
 
M

Mangesh Yadav

Hi BradHaberman

Please try the following function: Enter the following code in a standard
module. And then in a cell, use the function as follows:
=myArea(A1:A18,B1:B18)

this is assuming that:
1. Your values are in cells A1:A18, and directions are in cells B1:B18
2. You represent the directions as u, d, r, l (for up, down, right, left)


'------------------------------
Function myArea(myValues As Range, myDirections As Range)

If myValues.Cells.Count <> myDirections.Cells.Count Then
MsgBox "The 2 ranges you entered are not equal"
End If

n = myValues.Cells.Count

ReDim v(1 To n)
ReDim d(1 To n)

For i = 1 To n
v(i) = myValues(i)
d(i) = myDirections(i)

If d(i) = "d" Or d(i) = "l" Then
v(i) = -v(i)
End If
Next i

If Application.WorksheetFunction.Sum(v) <> 0 Then
MsgBox "The area does not close" & " " &
Application.WorksheetFunction.Sum(v)
End If

h = 0
a = 0
For j = 1 To n
If d(j) = "u" Or d(j) = "d" Then
h = h + v(j)
End If

If d(j) = "r" Or d(j) = "l" Then
a = a + (h * v(j))
End If
Next j

myArea = a

End Function

'------------------------------



Regards

Mangesh
 
Top