NaN% issue

[Former Member]

Hello,

Anyone have good suggestions on avoiding NaN% on pie chart when the value is zero?

I am using query browser to bring the values.

Thanks in advance.

Arun

[arijit_das · Accepted solution]

Better not to show the chart in this case and show a "No Data" Label instead.

How to do it ?

Suppose C3:D5 is the data grid. Write formula in E column as shown so that it displays blank if the data is 0. Now use E3:E5 as data in pie chart. Use ignore blanks at the end of range option in pie chart.

In D1, use a formula to get sum of the data set. If it is 0, then we need to show a no data label. I assume there will be no negative value as it is a pie chart.

Now add a label with text "No Data to display" and add dynamic visibility as show component when D1=0.

[Former Member · Accepted solution]

Thanks Arjith.

I was showing blank and the customer wants some chart with zero value to be displayed.

Is that possible?

Arun

[arijit_das · Accepted solution]

Zero value can be displayed. But what you expect as % ?

[former_member190855 · Accepted solution]

Hi Arun

Create a dynamic visibility logic like Arijit has suggested. When you have all zero value instead of showing a "No data Label" show a second Pie chart but in this one don't have the data label> Percentage enabled in Appearance tab. That way it will only show 0 for all the segments.

Thanks

Runali

[Former Member · Accepted solution]

Thanks Runali.

That works.

Arun

[Former Member]

Can you explain when you are seeing NaN% and is there any way replicate that behavior?

[Former Member]

When the value coming in are blank then NaN% appears.

[Former Member]

When you have 0 or no value on both columns the percentage displayed will be NaN%. Any way i could tweak this in excel?

Arun