Hide zero or error values in radar chart

[ExcelDane]

Hi everybody,

I am trying to hide zero or error values in a radar chart.
When I google this topic, most answers go something like

"replace =IF(a1>1;a1;"") with =IF(a1>1;a1;na())"

This returns an NA# value instead of a zero.

This solution might work in other chart types, but in my radar chart, a zero value is still plotted even if the underlying value is NA#.

Is there really no way to omit data points based on zero or error values in a radar chart?

I am using Excel 2007 for Windows.

 

[ExcelDane]

anyone?

 

[ExcelDane]

I ended up solving the problem myself.

Originally, the problem came up because the values for my radar chart were found in a table based on looked up values. Since a lookup function (or any other reference, for that matter) can never return a plain blank result, I had to make a workaround.

First, I made a proxy table next to my original one. My original table is placed in C21:E30. This is the one people will see. The proxy table is placed in the adjacent cells, F21:H30. I made it invisible by selecting white as font color. The diagram is based on this proxy table.

The original table is generated by looking up values in a database. The lookup key is the name entered in cell D2. Therefore, I made a worksheet_change event which runs a macro when cell D2 is changed.

The UpdateDiagram macro basically makes a proxy table, finds text values and deletes these text values.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$D$2" Then

Call UpdateDiagram

End If

End Sub

Sub UpdateDiagram()


Application.ScreenUpdating = False

Application.EnableEvents = False

    Range("F21:H30").Select
    Selection.FormulaR1C1 = "=RC[-3]"
    Range("F21:H30").Select
    Selection.SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.ClearContents

Range("D2").Select

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub