Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula(s) for area with more than four sides
I'm new to this type of thing (posting messages). I'm trying to create a formula(s) that will give me the area of a home with many exterior 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 it shouldn't be impossible, not that anything is impossible. Common sense says just to do it my head and write it down, but the program is for my neighbor, and I want to make it as simple as possible. Thanks Brad |
#2
|
|||
|
|||
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 "BradHaberman" wrote in message ... I'm new to this type of thing (posting messages). I'm trying to create a formula(s) that will give me the area of a home with many exterior 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 it shouldn't be impossible, not that anything is impossible. Common sense says just to do it my head and write it down, but the program is for my neighbor, and I want to make it as simple as possible. Thanks Brad -- BradHaberman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |