excel-counting points in a given area

[msucog]

anyone happen to know a good way to count the number of points within a given area? let's say you have a map of the u.s. and there are 10 areas identified with some overlap. there's say 100 points spread out through all the areas and i want to count how many points fall in each area. you're given the lat and lon of each point and know the lat and lon of the intersection of each line that create each area. the areas are oddball shapes (not simple squares) with up to 8 or 10 sides.

the only way i can think of is to have several if statements to evaluate if each point lies below the upper line, left of the right side, above the bottom line, etc. then count if all cells return "true" or "yes" or whatever the output is for each if statement. the problem i see is that due to the oddball shapes, the point may not be left of a particular line on the right side since it's technically below it. for example the "." are inside the area, the "X" is the point, and the other is the right side of the area.

............../
............./
............/
.........../
...........\
............\
.............\
............X.\
__________\

any suggestions?

 

[IDS]

A bit late perhaps, but the Excel spreadsheet IP.xls available from:

http://newtonexcelbach.wordpress.com/2008/08/10/intersections-interpolations-and-rotations/

includes an INSIDE() function that will check if a point is inside or outside a specified closed shape.

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au

 

[msucog]

thanks, i'll take a look.

the other methods worked well too depending on the way one likes to do it. i'm not too comfortable in vba so the cell equations do better for my simple self. i've just got to get all the equations down to a one cell equation.

 

[IDS]

"i'm not too comfortable in vba so the cell equations do better for my simple self. i've just got to get all the equations down to a one cell equation. "

Once you have picked up the basics, writing a UDF is actually much easier than writing a hyper-complicated one cell equation. It really is worth the effort to learn.


Just a suggestion

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au

 

[electricpete]

Doug produced a vba routine "inside".
Inputs: range containing polygone coordinates, range containing point coordinates
Output: 1 if point is inside, 0 if not.

I used Doug's routine in a spreadsheet (attached) to tabulate results for several points and several polygons. The pointn coordinates are vertical down the left side of the table, and the range containing the polygone coordinates are listed horizontally accross the top of the table. Each entry in the table grabs the corresponding point coordinates for it's row and the range of the polygon coordinates for it's column and gets a 1 or 0.

Should be self-explanatory to expand for your data. You shouldn't have to enter any ranges manually, just copy the formulas.

I tried to use all excel (except for Doug's vba). Got a little bogged down developing the range argument as you can see (although it works). I did have to resort to 1 more vba user-defined function evaluatexl to do what evaluate does in vba. One of the formula's works a little different differently in cell D5 than in the other cells to the right of it (returns the worksheet name along with the address)... I had to tweak the formula in D5 to compensate. I have no idea why that is. (any suggestions?)


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

OK, I figured out why cell Point!D5 acts differently. It's because Areas!B2 is different than the rest.

I fixed that in attached rev2.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

One more revision. I got rid of the vba function evaluatexl.... indirect() does the job. So only one vba function (Doug's) in the whole thing.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

A few comments on how the Inside UDF works.

The basics are quite simple, it counts the number of times a line drawn from the point, parallel to the x axis in increasing x direction, intersects the polygon. An odd number indicates the point is inside, an even number or zero indicates outside.

The problems are when the point is on or very close to a polygon edge (is it just inside or just outside?), and when the line exactly intersects a vertex (is that one crossing or two?)

The way I ended up going was to check if the point was within a small distance (1e-14) of the line, and if it was to treat it as being on the line, and therefore "inside".

On reflection this might not work well if the coordinates have a high value (such as typical grid coordinates). The "mindiff" constant could be changed to a greater value (say 0.0001, if working in metres), or possibly a better approach would be to divide the offset by the greater coordinate value, and treat anything less than 1e-14 as being zero.

For the case of the line intersecting a vertex I checked if both lines of the polygon were on the same side of the line (in which case the intersection was ignored), or if they were on opposite sides (in which case it was 1 intersection).

Interesting how complicated a simple problem gets when you have to program it into a dumb computer.

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au