Using Pythagoras theorem to count results

[demongeeks]

Hello all,
I have a spreadsheet of co-ordinates (Eastings & Northings). Columns A & B contain the co-ordinates of school premises. Columns E & F contain the co-ordinates of anti-social behaviour incidents. In column C I'd like to show a count of anti-social behaviour incidents occuring with 100m of the school (Easting & Northings are in metres which is handy)

Easting Northing Results Easting Northing
502871 107008 ------------- 518902 116729
585461 136574 ------------- 531108 108205
487650 135334 -------------

In my actual data I have about 464 anti-social behaviour incidents and 697 schools

I've worked out how to calculate the distance between school and incident using a pythagoras formula like this...

=SQRT((ABS(A2-D2))^2+(ABS(B2-E2))^2)

...but that isn't really what I need. What I really need is a count are incidents where the answer to this formula is >100m

Please help? I've tried everything I can think of.

[NBVC]

Do you mean something like:

=SUMPRODUCT(--(SQRT((ABS(A2:A10-C2:D10))^2+(ABS(B2:B10-E2:E10))^2)>100))

adjust ranges to suit.

[modytrane]

see sample attached.
I think in the following quote, you meant <100.

Please Login or Register  to view this content.

Anyway, your formula in Column C is fine. I have added a formula in C2 at the top of the column to give you a count of all results that are less than 100 meters.

modytrane

[shg]

The ABS is redundant. Just

=SQRT((A2-D2)^2 + (B2-E2)^2)

or

=SQRT(SUMSQ(A2-D2, B2-E2))