Hide 0 values in excel chart

[Gabriell]

Hi Experts!

I want to create a chart in Excel and hide the values that are zero.

The percentage is calculated using a formula. If I replace zero values with =NA(), the percentage cannot be calculated, and the chart becomes empty.

Is there any solution to fix this?

Thank you in advance!
Gabor

 

[Peter_SSs]

Something like this,
=IF(ISNUMBER(B21), B21/SUMIFS($B$21:$B$38, $B$21:$B$38, ">0"), NA())

Would there some advantage in using that much more complex formula than the ones from posts 2 & 7 which produce identical results for that column B data?

The ISNUMBER test is not required since #NA will already be returned if column B contains #N/A like your sample data. Wouldn't this do the same thing?

=B21/SUMIF(B$21:B$38,">0")

 

[Sam_D_Ben]

Would there some advantage in using that much more complex formula than the ones from posts 2 & 7 which produce identical results for that column B data?

The ISNUMBER test is not required since #NA will already be returned if column B contains #N/A like your sample data. Wouldn't this do the same thing?

=B21/SUMIF(B$21:B$38,">0")

As you mentioned, when I try using those formulas, this is the chart I'm getting. Please refer the chart below.

 

[Peter_SSs]

when I try using those formulas, this is the chart I'm getting.

It was interesting that you did not show us any of the table where rows had #N/A in column B. Here is mine with several of the short formulas, all returning the same results ...

Gabriell.xlsm
	A	B	C	D	E
20			Post 2	Post 7	Post 11
21	A	11	24%	24%	24%
22	B	17	37%	37%	37%
23	C	13	28%	28%	28%
24	D	#N/A	#N/A	#N/A	#N/A
25	E	1	2%	2%	2%
26	F	#N/A	#N/A	#N/A	#N/A
27	G	2	4%	4%	4%
28	H	1	2%	2%	2%
29	I	1	2%	2%	2%
30	J	#N/A	#N/A	#N/A	#N/A
31	K	#N/A	#N/A	#N/A	#N/A
32	L	#N/A	#N/A	#N/A	#N/A
33	M	#N/A	#N/A	#N/A	#N/A
34	N	#N/A	#N/A	#N/A	#N/A
35	O	#N/A	#N/A	#N/A	#N/A
36	P	#N/A	#N/A	#N/A	#N/A
37	Q	#N/A	#N/A	#N/A	#N/A
38	R	#N/A	#N/A	#N/A	#N/A
Chart (2)

Cell Formulas
Range	Formula
C21:C38	C21	=B21/SUM(IFERROR($B$21:$B$38, 0))
D21:D38	D21	=B21/AGGREGATE(9,6,B$21:B$38)
E21:E38	E21	=B21/SUMIF(B$21:B$38,">0")

.. and the resulting chart (using columns A & D in this case). No 0% sectors in the chart.

 

[Sam_D_Ben]

It was interesting that you did not show us any of the table where rows had #N/A in column B. Here is mine with several of the short formulas, all returning the same results ...

I reviewed all the formulas this morning, and everything is working correctly. The issue was on my end, I hadn’t shown the full view of the data. I zoomed in on the chart to make things clearer. The error was mine.

 

[Peter_SSs]

everything is working correctly. The issue was on my end,

Cheers. Thanks for clarifying.