Map - north america (excel)

**jw01** · 05-20-2011, 09:56 AM

Hello all,

I found a template online that showcased the world and a medal count for different cities on the world map.

Now, I'm trying to do something similar, where I am trying to show different cities on the North American map, and do a "bubble" graph to show their respective sales.

Ive managed to alter most of it and get the longitude and latitude for my cities, however im not sure how the calculations for the x and y axis are coordinated for them to appear on the map. can someone pls help me out! Ive been stuck on this for some time :S

ur help is truely appreciated! thxs...

cheers

plse see attached sample!

pls note the coordinates (longitude and latitude are of the states the cities are in......i have highlighted in "Red" where my isse lies!

**jw01** · 05-20-2011, 10:03 AM

this is the logic that is used behind the x-y coordinates. not sure how i can modify this...

"I had to convert latitude and longitude to y and x co-ordinates respectively so that I can plot them on 2 dimensions. I used this logic to do it:

x=(180+longitude)*(map-width/360)
y=(90-latitude)*(map-height/180)................"

**shg** · 05-20-2011, 10:21 AM

As I said in your other thread, simple formulas like that won't work unless you find a map in equirectangular (plate carree) projection. Yours looks like it might be Lambert Conformal Conic, but that's just a guess. The transformation of lat/long to LCC x-y position is pretty ugly.

**jw01** · 05-20-2011, 10:24 AM

Hello shg

Yeah, you are right. Mathematically, Im not sure how to convert it. I tried for instance, playing around by entering manual entries for "dallas" and managed to come close so its area.

not sure how the logic can work.

so i'm assuming, there is no other way :S

**Sibrulotte** · 05-20-2011, 11:54 AM

Wow, that's a nice little tool with the scroll bar. Never thought of that.

Is there a way to have the bubble size not the proportion of the whole (ie. Tried putting ridiculous amounts for ciites, and all the others went miniscule), but rather a predefined scale?

BTW, I realy thing you need to do them manually, trial and error with a colom that has values just for your city being "tuned".

**Sibrulotte** · 05-20-2011, 02:09 PM

The only trick I found was to have an extra line that is not part of your Hlookup with the max(datarange) value. That data point created is than the reference to all others, and is formatted not to have filling.

Works if you don't have a fluke number in there... like 18045 for Vancouver

**jw01** · 05-20-2011, 05:54 PM

hey thxs!

i managed to plug values in for the x and y manually. it was time consuming but it looks pretty neat in the end. thxs for the comments and help with the matter guys!