Avoid/Minimize Overlap via Automated Data Label Placement
I spent a couple hours trying to tackle this issue, but just couldn't work it out. Not a lot of help in Google-land, either.
I'd like to think there was already an algorithm that would automate the placement of datalabels on a chart, to avoid overlap (or minimize to a good extent, anyway). The goal on overlap would be to adjust the data label's position so the overlap goes away (without wandering too far from its marker). Some type of iterative/recursive process is undoubtedly needed.
Any ideas? The chart I've been working is a scatterplot. It would be really 'cool' if there was an automated way to handle.
'-------------------- BACKGROUND FOR NON-EXCEL EXPERTS
For the mathematicians: Think of a chart with data points that have labels. Here are the stats:
for the Excel experts, I may misstate but to avoid confusion, here are the facts to use as I can translate to code. I'm typing this off the top of my head after just having done this a couple days ago.
A chart has a each a Top, Left, Width, and Height Each of these gives the x,y coordinates of where the chart resides on the screen.
A data label starts out having a position to the right of the data point (could be other positions, but this doesn't matter for this exercise.) For completeness, I can also derive the points x and y coordinates.
A data label has each a Top, Left, Width, and Height as well (yes, these are derived, but I know how to derive width/height - so let's keep this simple)
So, now we have all the information we need to understand the spacial aspects of the problem and can visualize many scenarios.
I was thinking perhaps using some kind of 1/(distance between squared) kind of like gravitational pull to understand how close the data labels might be to each other. If we can understand that, perhaps we can "push" those closest away from each other?
Another thought is that we don't want the data labels to get too far from the points they are describing, and perhaps that's just another datapoint that we can limit percent change in the algorithm.
Finally, we may not be able to have a result that is 100%, but if we get 90%, a tweak by the user can finish (so what is going on in our brains to allow that tweak?)
I hope this helps generate some ideas and perhaps frames up a few algorithms to try.
Dave
I'd like to think there was already an algorithm that would automate the placement of datalabels on a chart, to avoid overlap (or minimize to a good extent, anyway). The goal on overlap would be to adjust the data label's position so the overlap goes away (without wandering too far from its marker). Some type of iterative/recursive process is undoubtedly needed.
Any ideas? The chart I've been working is a scatterplot. It would be really 'cool' if there was an automated way to handle.
'-------------------- BACKGROUND FOR NON-EXCEL EXPERTS
For the mathematicians: Think of a chart with data points that have labels. Here are the stats:
for the Excel experts, I may misstate but to avoid confusion, here are the facts to use as I can translate to code. I'm typing this off the top of my head after just having done this a couple days ago.
A chart has a each a Top, Left, Width, and Height Each of these gives the x,y coordinates of where the chart resides on the screen.
A data label starts out having a position to the right of the data point (could be other positions, but this doesn't matter for this exercise.) For completeness, I can also derive the points x and y coordinates.
A data label has each a Top, Left, Width, and Height as well (yes, these are derived, but I know how to derive width/height - so let's keep this simple)
So, now we have all the information we need to understand the spacial aspects of the problem and can visualize many scenarios.
I was thinking perhaps using some kind of 1/(distance between squared) kind of like gravitational pull to understand how close the data labels might be to each other. If we can understand that, perhaps we can "push" those closest away from each other?
Another thought is that we don't want the data labels to get too far from the points they are describing, and perhaps that's just another datapoint that we can limit percent change in the algorithm.
Finally, we may not be able to have a result that is 100%, but if we get 90%, a tweak by the user can finish (so what is going on in our brains to allow that tweak?)
I hope this helps generate some ideas and perhaps frames up a few algorithms to try.
Dave
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your responses.
@teylyn - nice, but that's not going to happen. You've got to be there... ;)
@aikimark - can the visio object model be used from within Excel and are all charts supported, or can one create a visio product on top of a graph to do the labeling?
Here's a before (top left)/after(bot right) simple example. Within the bounds of current axis, I'm needing an algorithm to get it more readable, the bot right is my manual adjustment to do that. TEST/CLI are at the same data point.
This is more about the algorithm than this chart. I see this with lots of charts that folks delivering don't have control over - they just have to generate.
Dave
Picture1.png
@teylyn - nice, but that's not going to happen. You've got to be there... ;)
@aikimark - can the visio object model be used from within Excel and are all charts supported, or can one create a visio product on top of a graph to do the labeling?
Here's a before (top left)/after(bot right) simple example. Within the bounds of current axis, I'm needing an algorithm to get it more readable, the bot right is my manual adjustment to do that. TEST/CLI are at the same data point.
This is more about the algorithm than this chart. I see this with lots of charts that folks delivering don't have control over - they just have to generate.
Dave
Picture1.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had developed a couple algorithms way into the night when it occurred to me to look and I could find nothing in Google-land on it, which prompted me to post, here. I'm a bit dissappointed there weren't any on the math/science side of this to pick up on it.
Thanks for picking up on that in the part about your algorithm thoughts. Thats what I'm after as opposed to chart design, because its not THIS chart or THAT chart, but ANY chart that might need this, may have legend, may not, may have color, may not, etc. Linking data points to the point nodes is a good idea, and can be done whether Excel supports or not - just draw it on the chart via VBA, if needed, I suppose.
Any email for Jeff you can share? Mine's on my profile.
Thanks alot. Its intellectually stimulating and we'll see where it goes from here.
Dave
=====
emailed it to you -- aikimark
Thanks for picking up on that in the part about your algorithm thoughts. Thats what I'm after as opposed to chart design, because its not THIS chart or THAT chart, but ANY chart that might need this, may have legend, may not, may have color, may not, etc. Linking data points to the point nodes is a good idea, and can be done whether Excel supports or not - just draw it on the chart via VBA, if needed, I suppose.
Any email for Jeff you can share? Mine's on my profile.
Thanks alot. Its intellectually stimulating and we'll see where it goes from here.
Dave
=====
emailed it to you -- aikimark
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the help. I'll ping back here when I have something as this is on the back-burner at the moment.
Dave
Dave
Hey Dave, I have a simple line chart that has three series. The data labels overlap, did you ever find a away to automate the labels to not overlap? Thanks buddy!
example.docx
example.docx
Omg- that is such horrible news! Dave helped me in so many ways.. I know he was fighting cancer as we skyped many times. Sad day here! Thanks for your response.
can you post an example of labels done right?
There are approaches to such an automated layout. You see the effects of such methods in products like Visio, when shapes are distributed automatically.