Empty Cells cause a line to zero in Radar Charts not based on a Pivot Table

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
Hola

I am struggling with a Radar Chart to eliminate the line between datapoints in case some datapoints being empty.

A) I created a table referencing data from a Pivot Chart and added a Displayed Values Average and an Whole Data Average columns. Selecting that table as basis for Radar Chart causes lines for some datapoints zero as no value given. I found an advice to use a formula to replace empty values in an if formula with #NV but that didnt work for me. Neither could I put in If(...,#NV ,...) nor If(...,"#NV",...)

B) Creating a Radar Chart based on the Pivot Table itself creates a chart with gaps wherever there is a missing datapoint value but it does not allow me to add a data series for personal and over all average although at least the Displayed Values Average is part of the Pivot Table already

Does anyone have an idea, how I can get what I want?
--
Regards Michael
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
... may be I should ask

A) How can I cause #NV as a result of an IF formula, something like If(...,"#NV ",...)
or
B) How can I add data series into a chart if the Select Data Dialog (for a Pivot Chart) is completely greyed out?
--
Regards Michael
 
Last edited:

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
In English, =NA() puts #N/A into a cell. So your formula would look like =IN(...,NA(),...)

#NV seems to be the German equivalent of #N/A. My first guess would be to use NV() where I used NA(). If that doesn't do it, well, I've pointed you in the right direction.
 

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
Thank you Jon Peltier,

and yes it eliminates the lines to the center

but

it does not interrupt the line for as many axis which do not carry any value.

Once again, if I create the same kind of chart directly connected to the Pivot Table, these lines are interrupted for not given values. Unfortunately in this case I cant display the average over all data nor the average of the displayed selection as I cant edit the data selection dialog.
--
Regards Michael
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

The pivot chart leaves a gap between points because the pivot chart contains blank cells. Formulas can't do that, unless you're on Excel 2016 with the latest updates applied, in which case the #N/A can be configured in a chart to act like a blank cell.
 

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
Thanx Jon

for the explanation.

If so, is there any opportunity for me to to include the 'Average Of The Selected' data series as well as the 'Average Over All' data series within the pivot table already?
--
Regards Michael
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I don't know what you're asking.
 

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
I understand. Below is a part of the underlying Pivot Table (don't mind the question marks in the first 3 rows).

If in column D and E the average of the displayed columns (12 and 52) and the average over all data rows (as only data row 12 and 52 are displayed) could be part of the Pivot Table that should solve my problem. I would not have to base the Pivot Chart indirectly to a separate table where right now I merge these values together.

Performance Report Selection

ABC
1Supervisor
????
(Multiple Items)
2Section
???
(All)
3Teacher
????
Liu.Xinzi
???
4
5 Column Labels
6Values1252
71.8280
82.90
93.82
104.85
115.8175
126.85
137.78
148.7085
159.
1610.8585
1711.
1812.85
1913.8390
2014.85
2115.81
2216.7790
2317.70
2418.80
2519.80
2620.7875
2721.
2822.80
2923.
3024.
3125.75
3226.9065

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:143px;"><col style="width:129px;"><col style="width:21px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

--
Regards Michael
 
Last edited:

Michael Kensy

New Member
Joined
Sep 27, 2017
Messages
25
(sorry for another post, I already past the 10 minutes correction timeline)

I know that via Pivot Table Options I can get the Average of the displayed data rows (12 and 52) integrated into the Pivot Table as column D but I don't know how the Average over all data can be displayed in column E.
--
Regards Michael
 

Watch MrExcel Video

Forum statistics

Threads
1,123,423
Messages
5,601,574
Members
414,460
Latest member
uctc

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