Charting Continuous line of discontinuous data of a pivot table

[bagullo]

hi --

I want to chart Pressure against flow at different temperatures, for experimental data and its equivalent value calculated with a regression.
of course for the exact flow value I will only have pressure at one of the temperatures in which data is taken, thus the rest remain empty
I know that in line charts, if a value is #N/A the line just goes to the next point. Thus I set the pivot table to show #N/A for empty values

If I chart as line chart, this works perfect, but the problem starts when I want to draw it as an XY chart (after all flow values are not even equally spaced)

Pivot chart is out of the question because exel does not allow it.

But creating XY chart reading the same data of the pivot chart does not solve the problem and inexisting data is shown as 0, nevermind what I put in the "empty cells" value

I even tried to create a calculated field, so that when P is #N/A it should be #N/A as well, but I only get zeros there --


any easy way to do it -- or the only way is to copy data to another sheet replacing data as I wish and so on?

pivot.xlsx

[Andy Pope]

The #N/A in the pivot tables are just the number format for zero. The cell actually contains zero.

You need to build a seperate data source for the chart where zeros are converted to =NA() formula.

[bagullo]

Thank you so much.
Apparently for pivotCharts it does work (it believes its own lie) , but for everything else must do as you say
I see that only if one of the coordinates is NA() the point is already skipped, which is good

Best Regards!