Creating line chart gaps with formulas

[Jova]

Hi,

I have a line chart that charts 35 cases.

The source data for this chart is created from formulas, that lookup the data in a larger dataset. The formulas are necessary because the chart is dynamic and the range changes upon user interaction.


Here is my problem: In my original dataset some cases have no value.

Now, when my formulas lookup for such a case, it returns an error.

I want that a case with no data would create a gap in my linechart. Instead it dips to 0 or if I use #N/A as the error type it connects the two valid data points. Picture 1; series 1 and 2 respectively.

PICTURE 1: http://i50.tinypic.com/2ensxlk.jpg


By googling the hell out of my problem, I found out:

- linechart will only create a gap for a blank cell

- a formula can never create a "truly blank" cell. A formula always returns something

- if the data set for line chart starts with a #N/A error, it will indeed not chart until the first valid data point.

- similarly, if the dataset ends with #N/A error it will cut off after last valid data point. Look at series 3 on picture 1.


So I figured this could be my solution; I must break the data for the cahrt into series of consecutive valid data points, and then format all the series the same, to fake the gaps

PICTURE 2: http://i49.tinypic.com/29pp69v.jpg


Now, based on my original range (35 cases) with lookup formulas, I need to create 18 dummy series (18 for "worst" case scenario if every other case is missing value).

What should be the formulas for my dummy series?


Aditional info:

- the original formula is Index, with named ranges, and dynamic parameters

- there can be one or more than one consecutive valid value cases

- there can be one or more than one consecutive no value cases

- the series can start and end with a one or more valid point or with one or more missing values (error).


I hope you can help me find a solution, because I could not find anything relavant on the www although many seem to search for it.


Jova

 

[Faseeh]

Jova,


Please upload a sample file instaed!! You can avoid simply replace error got by looking up DivError using IFEROOR(UrCellRef,NA()).


Regards,

 

[Luke M]

Hi Jova,

Before we head down the path of creating multiple dummy series and formulas, I'll offer up one more idea.


What if we used a short macro to copy the cells with formulas, paste to a new range (as values) and truly clear all the cells with #N/A errors. We then have the chart plot from this data? You should still be able to do all the dynamic controls, we're just changing which area the chart is looking at.

As for the macro, we could either have you push a button, or connect it to a Change_event macro, so that the updates happen more automatically.

an example of what that macro might look like:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FormulaRange As Range
Dim ChartRange As Range
Dim CheckCell As Range
'Which cell(s) are we watching for a change?
Set CheckCell = Range("A1")
If Intersect(CheckCell, Target) Is Nothing Then Exit Sub

'Define our ranges
Set FormulaRange = Range("A2:E10")
Set ChartRange = Range("A20:E28")

Application.EnableEvents = False
FormulaRange.Copy
ChartRange.PasteSpecial xlPasteValues
On Error Resume Next 'In case there are no errors, ignore next step
ChartRange.SpecialCells(xlCellTypeConstants, 16).ClearContents 'Clear all cells with errors
On Error GoTo 0
Application.EnableEvents = True

End Sub

[/pre]

 

[Jova]

Thank you both for your answers.


Faseeh- if I understand correctly, your solution is to replace the error with a different error?

If so, as I already decribed above- anything that is not "truly blank" (a go to cell and press delete kind of blank) will chart. Some errors chart as zero, others interpolate between available valid values. Take a look in the sample workbook.


Luke M thank you for your solution. I have thought about VBA or macro, only I am a complete noob at this stuff ...

But I tried your solution- I recored a macro and linked it to the form control. It works, but every now and then it reports a run time error.

But anyways I have several form controls, so I would prefer if you could help me make a workbook change macro.


Here is my sample workbook:

http://speedy.sh/Rvpsg/Sample-workbook.xlsm


Best, Jova