Trend Line X Axis longitude problem

ShelleyBelly

New Member
Joined
Mar 2, 2011
Messages
44
Hello Everyone,

Many thanks in advance. I have an issue where by a data set on a XY scatter graph is joined with Linear Trend line. It is used to show the routes a ship takes and the scatter graph has a map as a back drop, x axis is longitude and y is latitude.

The longitude (x axis) starts at -180 (west) to +180 (east).

Now the problem is when i make a route (trend line) that heads east from the eastern side, and exceeds +180 i'd like the route to the trend line to go up to +180 and then continue from -180 on the same latitude. The same goes for heading west in the western hemisphere passing -180.

At the moment the trend line draws right across the entire graph. A good example would be heading from Hawaii to Australia which would mean heading west, but at the moment it draws all the way across the globe eastwards, which not the actual route that would be taken.

essentially the x axis should almost be consider continuous ending at is maximum but then starting immediately from minimum again at the same y axis value.

I hope this makes sense.

I could ofcourse place intermediate xy data points at +180 and -180 to give the appearance of the trend line exceeding the max x value before starting again from the minimum and vice versa, but if there is a way in VBA then i think this would be a cleaner and more useable solution for all.

Many Thanks in advance,

Tom
 
Last edited:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
Daft Idea, could you do your plots as normal but on a parallel line create a rule that reflects every entry, but where greater than 180, model as (180 - 360)
 

ShelleyBelly

New Member
Joined
Mar 2, 2011
Messages
44
Hi mole999,

Not sure I get your idea, could you explain bit more?

At the moment the chart plots everything correctly, its the trend line which is the issue.

Cheers,

Tom
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
Ah, then what I was thinking isn't going to work, AFAIK Trend is purely left to right follow my chart link that site might know
 

Watch MrExcel Video

Forum statistics

Threads
1,099,064
Messages
5,466,380
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top