Hide zero or error values in radar chart

ExcelDane

Board Regular
Joined
May 14, 2009
Messages
82
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ExcelDane

Board Regular
Joined
May 14, 2009
Messages
82
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.

Code:
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
 

Forum statistics

Threads
1,136,300
Messages
5,674,961
Members
419,536
Latest member
Mohammed Jaffer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top