Custom DataLabels resetting

[LufiaMaxim]

I've got a graph and on one of the series, I've replaced the default datalabels with custom data labels.

Now for functionality, I've added a checkbox on my spreadsheet next to the graph to toggle these datalabels.

I've tried using HasDataLabels = True/False and DataLabels.ShowValue = True/False. Not sure if there's anymore ways to hide/show data labels, but that's what I've found so far.

In both cases, the data labels get reset, or, I lose my custom data labels. I toggle the labels off then on, and any formatting (I have my custom datalabels bold and 8pt font) is lost.

Is there any way to prevent this from happening? I know I COULD reapply the custom data labels, but we're talking about hundreds of data labels at this point and I'm worried about speed.

 

[Jon Peltier]

About the best I can think of is changing the font color of the labels to match the background, so they don't show up.

A more involved approach would be to have two series, one with the data points showing, and another without lines and markers, but which has the data labels attached to its hidden points. You can have the Y values of this series change between their real values and #N/A to show and hide the labels.

 

[LufiaMaxim]

A more involved approach would be to have two series, one with the data points showing, and another without lines and markers, but which has the data labels attached to its hidden points. You can have the Y values of this series change between their real values and #N/A to show and hide the labels.

Very interesting idea. One I certainly hadn't thought of.

Maybe I should be asking a different question instead... I have a main series with data points at (usually) 5 minute intervals as my main line on the graph. Small sample looks like this:

2011-06-16 01:21:39	130	1
2011-06-16 01:26:39	141	1
2011-06-16 01:31:39	123	1
2011-06-16 01:36:39	107	1
2011-06-16 01:41:39	99	1
2011-06-16 01:46:39	92	1
2011-06-16 01:51:39	83	1
2011-06-16 01:56:39	71	1
2011-06-16 02:01:39	56	1

This continues on over several days, equaling thousands of data points across several days.

Then on my 2nd series, I have much fewer data points, looking like this:

2011-06-15 20:41:55	1	3
2011-06-15 21:31:19	9	3
2011-06-16 05:08:48	3.5	3
2011-06-16 09:27:06	6.5	3

Ignore the last column, as I use these as EventIDs.

I should also mention, the 2nd series was originally graphed using a 2nd y-axis scale on the right side of the graph. The values of the main series range from 1-400 whereas the valuers on the 2nd series range from 1-20.

This is where it gets a little complicated. Originally I graphed these two series as-is and it worked just fine, except it wasn't exactly helpful to the end-user. What WOULD be helpful to the end-user is having the 2nd series' data points graphed ON the main series line on the graph.

Now I've done my own way of accomplishing this which works rather well speed-wise given the amount of data I have, but there may be a much easier way of doing it than what I've done.

So I pose a new question: If you can imagine how these two data series' would look on a graph, the 2nd series will be all over the place which is the expected behaviour. How would you accomplish getting the 2nd series placed ONTO (based on the time value) the main series' line, but still show the values for the 2nd series?

If this is all rather confusing, I'll try to clarify with some examples, or I could reverse my code to the point where it graphs the 2 data series as-is and show you that. For me, this would be hard to visualize, but I'm not the expert that you are.

 

[Jon Peltier]

So you want a marker for the second series on the corresponding point of the first series? You don't want to plot the second series values?

Why does the end-user want the second series plotted onto the first series? Is it just to know the horizontal positioning of the second series points? There must be a less misleading way to indicate that.

 

[LufiaMaxim]

So you want a marker for the second series on the corresponding point of the first series? You don't want to plot the second series values?

Correct. I don't want to PLOT the second series values, just SHOW them at the corresponding point of the first series.

Why does the end-user want the second series plotted onto the first series? Is it just to know the horizontal positioning of the second series points? There must be a less misleading way to indicate that.

To help understand, let me explain where this data comes from. The first series is a medical device that collects data in the blood every 5 minutes. The 2nd series is actions taken by the end-user on any out-of-the-ordinary values detected by this medical device. The user would (or should) correct any high value by giving themselves a certain amount of medication through another medical device. Both these devices are downloadable, which is how this data gets into a spreadsheet.

It's helpful for the user to see when they corrected these higher-than-normal values and how much they corrected it with. The idea being, if they over- or under-corrected, the graph would indicate that by the next few readings of the first medical device, allowing them to make adjustments on their medication based on this graph.

Let me pull my example graph from my first thread into here:

Note: Ignore the green markers, they are calibration values to make sure the medical device is still recording things accurately. The calibration values were no problem at all, which is why I haven't mentioned them yet.

This graph starts out with very high values, to which the user corrected these values by giving themselves 5 units of medication two times (the first two red dots). As you can see, the line went down drastically because of this correction, down to an acceptable range. This tells the end-user they did a great job of handling this high value.

It's a rather complex process, and without fully understanding how or where the data comes from, it might not make much sense.

My problem is that I "need" 3 data points (time, y-value for the first line, value to display) for the 2nd series, but have only two to work with (time, value to display). As you can see by the graph, I DO have a working method of getting these points on the line, but I'm just wondering if there's a better way to do it than what I'm currently doing.

 

[Jon Peltier]

Yes, now it makes sense.

I think I'd adda column next to your real data, and use a formula that returned the same value if the time matched, or NA() otherwise. Add it to the chart, format it red, and start on the next problem.

 

[LufiaMaxim]

That's pretty close to what I'm currently doing.

I think I'll simply stick what I currently have. My sample data that I'm using "only" covers 5000 values for the first data series and about 200 values for the 2nd series in question, but the end-user can very likely have 20,000 values and 1000 values, respectively.

Think it's time to generate some fake data and run tests on real-size data.

I will mention, though, that I'm probably going to use your method of having a 2nd series with the same label points which I show/hide for the checkbox-toggling feature. Currently, I reload all custom labels when the user has checked the box. With potential 1000-2000 values, that's probably not a very good idea. Therefore, having a 2nd series with the same data seems like a much more reasonable idea.

Thanks for the original idea and also for all the help you've given me.