DisplayBlanksAs = xlInterpolated for One Series Only

[JonMulder]

Greetings,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I'm using VBA to create over 200 hydrographs of water levels in monitoring wells. One seriescollection is continuous data (1 hour intervals by datalogger) and the other series is a manual measurement (about 4 times a year). I want the manual measurements to be interpolated so that a line connects the gaps between them, But I don't want the continuous data to interpolate gaps (i.e., when the data logger stopped recording).<o></o>
<o> </o>
It seems the "DisplayBlanksAs " is a property of the chart, but cannot vary between series. Any idea how to do this? I've started thinking of overlaying two chart objects on the same worksheet, but that seems kind of kludgy.<o></o>
<o> </o>
Any help would be appreciated!<o></o>
<o> </o>
Jon Mulder<o></o>
California Department of Water Resources<o></o>
Durham, CA USA<o></o>

 

[Cindy Ellis]

Hello, and Welcome to the Mr. Excel board!

There may be a better way for your situation, but I have 2 possible solutions.

First, given the volume of data logger data compared to the manual data, there's a workaround that may be "close enough" for your purposes. I'm assuming that you have time stamps in one column, a "complete" column of data logger data, a 3rd column with a couple thousand blank rows between manual measurements (that is, the manual data lines up with the equivalently time-stamped data logger data in the worksheet). If that's the case, try applying a "2-period moving average" trendline to the manual data. It will join the manual data with a straight line, offset by one row. Since 1 row equates to 1 hour (or so), it will likely be indistinguishable from a line joining the exact points.

The other approach may be what you meant when you mentioned overlaying 2 charts, but just in case...
This requires the data-logger data in 2 columns, with the manual data in 2 separate columns (date/time and water level) with no blanks between the manually recorded rows:
First, create the chart with the data logger data using the time-stamp for the x-axis and the data-logger data for the y-axis (scatter chart with line).
Then, in the "Data Source" dialog for the chart, choose Add, and add the 2 new columns, with the date/time column as X and the manual water level reading as y-axis values. Again choose a scatter chart with a line. The new data series should overlay the original, connected by lines.

This doesn't get into the VBA aspects of either of these. If either one of them makes sense for your situation and you need help with the VBA for it, let me know.

Hope that helps,

Cindy

 

[JonMulder]

Cindy,

Thanks for the quick response! Trendlines with a 2-period moving average did the trick!

I've never used trendlines. Now I know!

Much appreciated!