How NOT to graph Zero values on data labels on a secondary axis?

[Mark McInerney]

Hi All,

I have combo graph in Excel 2013 - the secondary axis is the percentage up or down of sales year on year. As we are mid way thru the year some of the data is 0. I have an If formula that populates a #n/a if the value is 0 - this works on my normal primary axis mapping of sales weekly over the last couple of years. The secondary axis graph is fine, but when I add labels, it also populates the #n/a value on the label, whereas I need it to be blank?

I was also wondering if it is possible to select certain data to appear in the data table below the graph? When I add a data table I get all data elements, whereas I want only the secondary axis data to appear?

Any help - greatly appreciated.

Best - Mark.

 

[BeMike]

Try using IFError before your IF statement - this should eliminate the error #NA...

 

[Mark McInerney]

My problem is not what the output of the formula is, I can leave it as a "0" - my problem is that if my if then statement has a "0" output, I don't want the data label to populate - I do not want any label to appear if the value is zero.

The #n/a reference is only because when I line graph other data, the #n/a stops the graph from plotting along the 0 axis point.
BTW - Thanks for coming back to me.

 

[BeMike]

Apologies Mark - you could use a IFERROR statement to populate "#NA#" - this would then eliminate the ones which are 0 - I am sure this works for X and Y axis - not sure for secondary axis - could you try this changing it to X or Y...