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

[Michael Kensy]

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

 

[Michael Kensy]

... 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

 

[Jon Peltier]

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]

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]

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]

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]

I don't know what you're asking.

 

[Michael Kensy]

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

	A	B	C
Supervisor ????	(Multiple Items)
Section ???	(All)
Teacher ????	Liu.Xinzi ???
	Column Labels
Values

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:143px;"><col style="width:129px;"><col style="width:21px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]52[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: left"]1.[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: left"]2.[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: left"]3.[/TD]
[TD="align: right"]82[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: left"]4.[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: left"]5.[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: left"]6.[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: left"]7.[/TD]
[TD="align: right"]78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: left"]8.[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: left"]9.[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: left"]10.[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: left"]11.[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: left"]12.[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: left"]13.[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: left"]14.[/TD]
[TD="align: right"]85[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: left"]15.[/TD]
[TD="align: right"]81[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: left"]16.[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: left"]17.[/TD]
[TD="align: right"]70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]
[TD="align: left"]18.[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]25[/TD]
[TD="align: left"]19.[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]26[/TD]
[TD="align: left"]20.[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]27[/TD]
[TD="align: left"]21.[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]28[/TD]
[TD="align: left"]22.[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]29[/TD]
[TD="align: left"]23.[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]30[/TD]
[TD="align: left"]24.[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]31[/TD]
[TD="align: left"]25.[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]32[/TD]
[TD="align: left"]26.[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]65[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

--
Regards Michael

 

[Michael Kensy]

(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