Hi,
I have 2 columns of longitude and latitude coordinates. I want to count coordinates within certain ranges. A bit like using the Frequency function but for 2 sets of values and 2 bins arrays. This would mean I could create a table with latitute bins as the row headers and longitude bins as the column headings. Would anyone know how to do this?
Cheers
Mystery
how are coordinates entered? i.e what format
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
My coordinates are expressed as decimal degrees in 2 columns next to each other. So I chose a set interval for my bin arrays, to try to count the population in each area that is created from the 2 bin arrays. I don't know where I'm going with this. I thought there might be a more copmplex frequency function or something???
A sample file would be useful... a Pivot Table may be viable... failing that possibly Sumproduct but I would certainly investigate a PT first as it will be the more efficient.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I can't seem to upload a sample file for some reason. It's under the limit? I'll try later. A pivot table sounds like the right sort of thing but how to I get each of my individual coordinates into a bin to start with?
If the co-ordinates are numeric you should find you can create the "bins" by grouping the Fields in the PT by a chosen interval.... I've taken the liberty of uploading a very simple demonstration of the technique as a proof of concept.
Ah I'm there!!! Thankyou with the Pivot table nudge. I used the VLOOKUP to sort group coordinates into my own bins, then a Pivot table based on these. Ah sweet relief.
Cheers
Ahh that way works too. How do you change the intervals in the Pivot table, I'm a novice in this area to be honest.
In order to use Grouping on the PT Field(s) the Interval for a given field would need to be constant.
If that's the case then to set/alter the Grouping interval - right click on the field of interest -> Group and Show Detail -> Group -> Set Start/End/By values as desired.
If the intervals are not consistent then your current approach of using an additional column @ source to create the bins is likely to be the best option available to you.
Thankyou for your help. I've now got a very pretty map of the UK with conditional formatting and tweeking of column and row widths.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks