Irregular Polygon Area

I

icb12

New Member
Joined
Sep 15, 2009
Messages
9
I have a need to calculate the area of an irregular polygon.
Using the area by coordinates method. Given that you have the x,y coordinates of every vertice.

The equation is as follows..
A=(1/2)[{(x1*y2)+(x2*y3)+(x3*y4)...(xN*y1)} - {(y1*x2)+(y2*x3)+(y3*x4)...(yN*x1)}]

Assuming the vertice coordinates are (x1,y1), (x2,y2),...(xN,YN)- listed in separate columns on sheet1

This is simple enough to do in excel when the polygon has just a few vertices.
But when the closed traverse (polygon) is huge with tons of vertices the equation becomes convoluted and a PITA.

I'd like to write a function in VB that would allow me to quickly and easily compute the area simply by selecting the coordinates I have entered on the sheet1.

This was suggested to me by someone more skilled than I... but I can't seem to make it work.
Public Function getArea(ByVal a As ListBox, ByVal b As ListBox)

' Make sure to add the first element to the end to close the polygon
a.Items.Add (a.Items(0))
b.Items.Add (b.Items(0))

Dim n, area, i
n = a.Items.Count

i = 0
area = 0

Do
area = (a.Items.Item(i) * b.Items.Item(i + 1) - a.Items.Item(i + 1) * b.Items.Item(i))

i = i + 1
Loop While i < n - 1

area = Math.Abs(area * 0.5)

' if you have a certain scale you can use this, or skip it
area = applyScale((9 / 64), area)

Return area
End Function
Click to expand...
Any suggestions? On how to make the above work, or a better way?

I appreciate any and ALL assistance.

-Iain
 
Sort by date Sort by votes
try:
Code: 
Public Function getArea(x, y)
Dim n, area, i
n = x.Cells.Count
If x.Columns.Count <> 1 Or y.Columns.Count <> 1 Or n <> y.Cells.Count Or n < 3 Then Exit Function
For i = 1 To n - 1
  area = area + (x(i) * y(i + 1) - x(i + 1) * y(i))
Next i
area = area + (x(n) * y(1) - x(1) * y(n))
getArea = Math.Abs(area * 0.5)
' if you have a certain scale you can use this, or skip it
'getarea = applyScale((9 / 64), getarea)
End Function
used on the worksheet thus:
=getarea(J4:J19,K4:K19)

=getarea(xvals,yvals)

xvals and yvals should each be ranges, 1 column wide, the same number of cells in each.
 
Last edited:
Upvote 0
A little more flexible; there only has to be the same number of cells for xvals and yvals, each range does not need to be contiguous (but to match xs with ys you need to select/compose the cells in corresponding order). The ranges can be horizontal or vertical, or mixed, eg.:
=getarea((A24:A26,A29:A32,A34:A42),(B24:B26,F29:I29,D36:D44))

Code: 
Public Function getArea(x, y)
Dim n, area, i, a(), b()
n = x.Cells.Count
If n <> y.Cells.Count Or n < 3 Then Exit Function
ReDim a(1 To n): ReDim b(1 To n)
i = 1
For Each cll In x.Cells
  a(i) = cll.Value: i = i + 1
Next cll
i = 1
For Each cll In y.Cells
  b(i) = cll.Value: i = i + 1
Next cll

For i = 1 To n - 1
  area = area + (a(i) * b(i + 1) - a(i + 1) * b(i))
Next i
area = area + (a(n) * b(1) - a(1) * b(n))
getArea = Math.Abs(area * 0.5)
' if you have a certain scale you can use this, or skip it
'getarea = applyScale((9 / 64), getarea)
End Function
 
Upvote 0
Hi

An easy way to calculate the area of the polygon is to close it (adding again the first vertice at the end).

In this case, the vertices of the polygon are in rows 2:6. As you can see I added the first vertice again at the end.

The area, in E2:

=1/2*ABS(SUMPRODUCT(A2:A6,B3:B7)-SUMPRODUCT(B2:B6,A3:A7))


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"> </TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>D</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>E</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>F</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">X</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Y</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Area</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; FONT-WEIGHT: bold; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; FONT-WEIGHT: bold; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">8</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=7>[Book2]Sheet1</TD></TR></TBODY></TABLE>
 
Upvote 0
p45cal said:
A little more flexible; there only has to be the same number of cells for xvals and yvals, each range does not need to be contiguous (but to match xs with ys you need to select/compose the cells in corresponding order). The ranges can be horizontal or vertical, or mixed, eg.:
=getarea((A24:A26,A29:A32,A34:A42),(B24:B26,F29:I29,D36:D44))

Code: 
Public Function getArea(x, y)
Dim n, area, i, a(), b()
n = x.Cells.Count
If n <> y.Cells.Count Or n < 3 Then Exit Function
ReDim a(1 To n): ReDim b(1 To n)
i = 1
For Each cll In x.Cells
  a(i) = cll.Value: i = i + 1
Next cll
i = 1
For Each cll In y.Cells
  b(i) = cll.Value: i = i + 1
Next cll

For i = 1 To n - 1
  area = area + (a(i) * b(i + 1) - a(i + 1) * b(i))
Next i
area = area + (a(n) * b(1) - a(1) * b(n))
getArea = Math.Abs(area * 0.5)
' if you have a certain scale you can use this, or skip it
'getarea = applyScale((9 / 64), getarea)
End Function
Click to expand...

Excellent! Thank you.
pgc01 said:
Hi

An easy way to calculate the area of the polygon is to close it (adding again the first vertice at the end).

In this case, the vertices of the polygon are in rows 2:6. As you can see I added the first vertice again at the end.

The area, in E2:

=1/2*ABS(SUMPRODUCT(A2:A6,B3:B7)-SUMPRODUCT(B2:B6,A3:A7))


<table style="border-color: rgb(204, 204, 204); border-width: 2px; background: rgb(255, 255, 255) none repeat scroll 0% 0%; border-collapse: collapse; font-family: Arial,Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; font-size: 10pt;" border="1" cellpadding="1"><tbody><tr><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">A</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">B</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">C</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">D</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">E</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="30">F</th></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">X</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">Y</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">Area</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">7</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">6</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">6</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">7</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right; font-weight: bold;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right; font-weight: bold;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; text-align: center; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">8</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">
</td></tr><tr><td style="background: rgb(153, 204, 255) none repeat scroll 0% 0%; padding-left: 1em; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" colspan="7">[Book2]Sheet1</td></tr></tbody></table>
Click to expand...

Right.. I know about adding the first coords on at the end part. See the equation in OP..
But I did not Know about SUMPRODUCT.. that is awesomely great information. I can actually do what I need to do without VB.. stupidly simple. I feel kinda dumb now.

Big Thanks to both of you!!
 
Upvote 0
Iain, I'm glad it helped.

Notice that you can also do it without adding the vertex at the end, but in that case the formula is not so simple.

Ex.: The same polygon, but without repeating the first vertex, the area in E2:

=1/2*ABS(SUMPRODUCT(A2:A6*N(OFFSET(B2,MOD(ROW(B2:B6)-ROW(B2)+{1,-1},ROWS(B2:B6)),0))*{1,-1}))


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"> </TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>D</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>E</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>F</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">X</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Y</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Area</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=7>[Book2]Sheet1</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
pgc01 said:
Hi

An easy way to calculate the area of the polygon is to close it (adding again the first vertice at the end).

In this case, the vertices of the polygon are in rows 2:6. As you can see I added the first vertice again at the end.

The area, in E2:

=1/2*ABS(SUMPRODUCT(A2:A6,B3:B7)-SUMPRODUCT(B2:B6,A3:A7))


[TABLE="width: 2"]
<tbody>[TR]
[TH] [/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Area[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"][Book2]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...




A really nice solution. My data comes in a slightly different format though...I wonder if you might be able to adapt this solution for it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]x1[/TD]
[TD]y1[/TD]
[TD]x2[/TD]
[TD]y2[/TD]
[TD]x3[/TD]
[TD]y3[/TD]
[TD]x4[/TD]
[TD]y4[/TD]
[TD]x5[/TD]
[TD]y5[/TD]
[TD]x1[/TD]
[TD]y1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I have again added the first x and y coordinate at the end also.

I am trying to decide whether I can find a simple excel fix, or if I really need to get this into a program like R and get things done there. I tried modifying your formula to this arrangement, but I have not yet been successful. The data looks like this because each row is an individual item that I need to obtain the area of I have 700 items (fossil fish!) It would make my life easier if I didn't need to change the format of the data...as the rows also contain lots of other information (age, locality etc).

Thank you very much!

John
 
Upvote 0
Hi John

Just changing the formula to your format, I got:

=1/2*ABS(SUMPRODUCT(A2:I2*ISEVEN(COLUMN(A2:I2)-COLUMN(A2)),D2:L2)-SUMPRODUCT(C2:K2*ISEVEN(COLUMN(C2:K2)-COLUMN(C2)),B2:J2))


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >J</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >K</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >L</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >M</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">x1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">y1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=14 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

Remark: maybe the formula can be simplified. This was just a direct adaptation of the previous one.
 
Upvote 0
Thank you for that, now I just need to understand the formula a little better so I can apply it to my larger set of x and y values.
Should be fine once I play with it a little bit. Not sure what "ISEVEN" is all about.

Thank you!






pgc01 said:
Hi John

Just changing the formula to your format, I got:

=1/2*ABS(SUMPRODUCT(A2:I2*ISEVEN(COLUMN(A2:I2)-COLUMN(A2)),D2:L2)-SUMPRODUCT(C2:K2*ISEVEN(COLUMN(C2:K2)-COLUMN(C2)),B2:J2))


[TABLE="width: 2"]
<tbody>[TR]
[TH] [/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[TH="width: 30, align: center"]H[/TH]
[TH="width: 30, align: center"]I[/TH]
[TH="width: 30, align: center"]J[/TH]
[TH="width: 30, align: center"]K[/TH]
[TH="width: 30, align: center"]L[/TH]
[TH="width: 30, align: center"]M[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]x1[/TD]
[TD="align: center"]y1[/TD]
[TD="align: center"]x2[/TD]
[TD="align: center"]y2[/TD]
[TD="align: center"]x3[/TD]
[TD="align: center"]y3[/TD]
[TD="align: center"]x4[/TD]
[TD="align: center"]y4[/TD]
[TD="align: center"]x5[/TD]
[TD="align: center"]y5[/TD]
[TD="align: center"]x1[/TD]
[TD="align: center"]y1[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="colspan: 14"] [Book1]Sheet3[/TD]
[/TR]
</tbody>[/TABLE]


Remark: maybe the formula can be simplified. This was just a direct adaptation of the previous one.
Click to expand...
 
Upvote 0
You're welcome. Thanks for the feedback.

Remark: I use IsEven() to skip the values in every other column.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Creating and exporting moving average values from multiple series
Replies
0
Views
277
Dooarazumas
D
S
  • Solved
#Name? error when using user defined function
6 7 8
Replies
74
Views
5K
CSmith
CSmith
Jaafar Tribak
  • Question Question
Date Input Mask ( Applying a Watermark to TextBox)
Replies
9
Views
3K
Davmacrat
Davmacrat
B
  • Solved
VBA based multiple column index-match macro that uses user inputs.
Replies
5
Views
1K
Bassie
B
M
  • Question Question
Small optimization task in Excel (Solution in AMPL )
Replies
0
Views
1K
MistaMista
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top