Finding a Polygon Area

[mikeazer]

I have an Excel Sheet with several coordinates (x,y). I want to find the area of the polygon formed by connecting these coordinates together.


A simple formula to find this area is shown in Wikipdeia


If I have two columns, one for the Xs and one for the Ys, what would be the simplest formula that would calculate the area.

 

[Luke M]

You could install this UDF in the VBE. Right click on sheet tab, view code. Click on Insert - Module. Paste this in.

[pre]

Function PolygonArea(X_Range As Range, Y_Range As Range)
Dim N As Integer
N = X_Range.Count
If N <> Y_Range.Count Then
PolygonArea = "Unequal Points"
Exit Function
End If

Dim xFirst As Integer
Dim xSecond As Integer
Dim QuickSum As Double
For i = 0 To N - 1
If i = 0 Then
xFirst = N
Else
xFirst = i
End If
xSecond = i + 1

QuickSum = QuickSum + X_Range.Cells(xFirst, 1).Value * Y_Range.Cells(xSecond, 1) _
- X_Range.Cells(xSecond, 1).Value * Y_Range.Cells(xFirst, 1).Value
Next i

PolygonArea = Abs(0.5 * QuickSum)

End Function

[/pre]
Then, in your workbook, you can write a formula like:

=PolygonArea(A2:A10,B2:B10)

 

[NARAYANK991]

Hi Mike ,


Luke has already given a solution. However , can you confirm whether the following will work ?


Assume your x-range is H5:H200 ; assume your y-range is I5:I200.


If your values of H200 and I200 are not the same as H5 and I5 , copy the value of H5 into H201 , and the value of I5 into I201.


Use any unused cell to put in the following formula :


=0.5*SUMPRODUCT((H5:H200)*(I6:I201)-(H6:H201)*(I5:I200))


Narayan

 

[Luke M]

Narayank,

Ooh, I like it! There is one snag...The Surveyor formula requires that X_o point is the same as the X_n point. So, somehow, need the formula to loop around to the beginning somehow...

 

[NARAYANK991]

Hi Luke ,


Thanks. That is the reason I asked Mike to copy the first cell to the last cell in the two ranges. So H201 is the same as H5 , and I201 is the same as I5 , assuming that H200 is different from H5 , and I200 is different from I5 ( as an example ).


By copying the first cell to the last cell in the two ranges , the overall result should not be affected , but at the same time we will be taking care of the requirement x(i)*y(i+1) and x(i+1)*y(i).


It'll be nice if Mike can confirm if it works or doesn't.


Narayan

 

[Luke M]

Oops, missed that bit. Nice job. =)

 

[mikeazer]

@Luke & Narayank: Thank you so much for replying! I apologize for not responding earlier, I didn't realize that I got any replies. This is the first time I use the forums, and I thought I would be receiving emails to indicate I got replies.


@Luke: Thanks for your reply, beautiful and clear code, but I was actually looking for a "normal" formula not requiring VBA.


@Narayank: Thanks I tried it with a simple triangle (0,0), (1,1), (2,0) and it worked! I just needed to change it to =-0.5*SUMPRODUCT((H5:H200)*(I6:I201)-(H6:H201)*(I5:I200)), because my coordinates are in a clockwise manner, while the equation solves in an anti-clockwise manner.

 

[NARAYANK991]

Hi Mike ,


Thanks for the feedback.


Narayan

 

[John_Clarke]

@NARAYANK991


Having tried your formula:


=0.5*SUMPRODUCT((H5:H200)*(I6:I201)-(H6:H201)*(I5:I200))


I get an #N/A error.


It doesn't seem to like the fact that the arrays in the formula are of different lengths, but surely this is intentional. Any thoughts?


Thank you,


John

 

[Luke M]

Hi John,

The arrays are actually the same size (or at least, should be), they are just shifted by 1 row. Do you have any text values in the ranges that could be messing up the SUMPRODUCT formula?

 

[John_Clarke]

I was silly and was getting my ranges messed up, I wasn't shifting the formula by one row. By correcting this, it meant that the formula in post 4 of this thread is now working for me:


http://www.mrexcel.com/forum/excel-questions/495100-irregular-polygon-area.html


However, with the formula =0.5*SUMPRODUCT((H5:H200)*(I6:I201)-(H6:H201)*(I5:I200))

I now get the #VALUE! error.


Either way, I now have 2 methods which are yielding the same result, that of the link above, and that from this link


http://maruzar.blogspot.co.uk/2011/12/irregular-and-regular-polygon-area.html