zeroes or interpolation in excel charts

[jen in vancouver]

In searching for a solution to my problem of zeroes plotting on my chart when my cell value was "" I read several posts on the boards about this topic. I even thought it was fixed when I found the advice to use NA() instead. I don't have zeroes plotting anymore, but now the chart is drawing a line from the last value present over to the next value (ie. doing a sort of interpolation). I want to have my line drawn between all numerical values and leave a blank area in the chart when the data is missing.

 

[PaddyD]

Hi welcome to the board!

It a bugger that, isn't it - excel out-smarts itself again!

Tushar's written an addin to generate the 'plot a gap in a line chart for a formula-returned blank' feature:

http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

 

[jen in vancouver]

[No message]

 

[jen in vancouver]

x-y charts are now also not working properly.
The add-in doesn't seem to solve the problem, and it slowed down the computer to the point of uselessness. (I only just realized this after spending half my day defragmenting, chopping out data & searching for spyware & viruses.)

The only solution which is working for me, is going cell by cell hitting delete to clear the contents of the formula present there which is returning either "" or #N/A. This isn't practical for a the thousands of data points I'm dealing with. Any other suggestions? (Copying & pasting values only to another column does not even work since those cells apparently aren't "empty" enough either.)

 

[tusharm]

You are welcome to not use the add-in but lumping it with spyware or viruses is uncalled for. It's legitimate software that has been downloaded by many to help deal with exactly the same problem that you have.

Yes, if your charts have thousands of data points, performance may lag since the add-in has to check every data point whether it has changed to, or from, a #N/A value.

x-y charts are now also not working properly.
The add-in doesn't seem to solve the problem, and it slowed down the computer to the point of uselessness. (I only just realized this after spending half my day defragmenting, chopping out data & searching for spyware & viruses.)
{snip}

 

[jen in vancouver]

For a low-tech user, it is amazing to me that someone can write (and freely give away) these useful pieces of software.

I didn't mean to insult the program. I didn't think I had implied it was spyware or a virus and I'm sorry you took it that way.

I didn't solve my problem. I need a different solution. I was hoping someone had one.

 

[tusharm]

I guess your first paragraph could be interpreted either way. We'll consider it as water under the bridge.

Below is a first pass at a solution that uses another column to create the data plot of interest. It should be faster than the add-in because it doesn't directly touch the chart letting XL do the necessary work.

The code goes in the worksheet code module and consequently is not something that I would use as a long term solution. But it will get you going. It assumes your data are in column A and column B is available as a temporary 'holding' column. Create the chart so that it uses column B. You can change both of those by changing the constants at the top of the code.

Option Explicit
Const OrigDataCol As Long = 1, _
    PlotDataOffset As Long = 1
Sub checkChartRange(aRng As Range)
    Dim aCell As Range
    On Error GoTo ErrXIT
    Application.EnableEvents = False
    For Each aCell In aRng.Cells
        If aCell.Value = "" Then
            aCell.Offset(0, PlotDataOffset).ClearContents
        Else
            aCell.Offset(0, PlotDataOffset).Value2 = aCell.Value2
            End If
        Next aCell
ErrXIT:
    Application.EnableEvents = True
    End Sub
Private Sub Worksheet_Calculate()
    checkChartRange Application.Intersect( _
        Columns(OrigDataCol), UsedRange)
    End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RngOfInterest As Range
    Set RngOfInterest = Application.Intersect( _
        Target, Target.Parent.Columns(OrigDataCol))
    If RngOfInterest Is Nothing Then
    Else
        checkChartRange RngOfInterest
        End If
    End Sub

The code above has been lightly tested.

For a low-tech user, it is amazing to me that someone can write (and freely give away) these useful pieces of software.

I didn't mean to insult the program. I didn't think I had implied it was spyware or a virus and I'm sorry you took it that way.

I didn't solve my problem. I need a different solution. I was hoping someone had one.

 

[jen in vancouver]

I think part of the trouble that the add-in had with my data is that I'm using two separate y-axes. Will this alternate method handle it?

Thanks

oh, and perhaps a note on the add-in page letting users know that the add-in may be slow with large data sets would eliminate the confusion for someone else. I thought the problem was my computer.

 

[tusharm]

I don't think the add-in did not have problems with your having two axis. If anything, it was a case of too much data for an event procedure.

Will the alternative handle the load? I have no idea. The easiest thing to do is check for yourself. After all, it will depend on your hardware and system configuration and what else you have running on the computer. Will it handle two series (a requirement for using two y axis)? No. But, unless you know it works for one, there is not much point enhancing it for two, is there?

I think part of the trouble that the add-in had with my data is that I'm using two separate y-axes. Will this alternate method handle it?

Thanks

oh, and perhaps a note on the add-in page letting users know that the add-in may be slow with large data sets would eliminate the confusion for someone else. I thought the problem was my computer.