Hide chart data labels with value zero

[Skotland]

Hey forum!

I am currently working on a chart which is showing time if the format hhmmss. I have some values showing no time (00:00:00). Is there any smart way to hide these values from the data labels?

Thanks in advance.

 

[JazzSP8]

Hi Skotland - Welcome to the Forum

You can hide zero values by using NA() which will mean they aren't plotted.

If the data being plotted is the result of a formula you can use it like this;

=IF([YOURFORMULA]=0,NA(),[YOURFORMULA])

Or, you can just make a formula and plot that range instead? For each of your current data cells make a copy row / column with;

=IF(A1=0,NA(),A1)

Does that help?

 

[Skotland]

Hi Skotland - Welcome to the Forum

You can hide zero values by using NA() which will mean they aren't plotted.

If the data being plotted is the result of a formula you can use it like this;

=IF([YOURFORMULA]=0,NA(),[YOURFORMULA])

Or, you can just make a formula and plot that range instead? For each of your current data cells make a copy row / column with;

=IF(A1=0,NA(),A1)

Does that help?

Hi Jazz!

Thank you for your answer. I'm already using an if statement in the formula, and it looks like this

=IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC LOGINTIME'!$H:$H;'SALES DASH'!M35);"00:00:00");"")

Is it possible to merge these two to give me the result im looking for?

Thanks!

 

[JazzSP8]

You don't need to merge it, looks like the 0 values are being put in place by your formula if there is an error?

So just change it to;

=IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC  LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC  LOGINTIME'!$H:$H;'SALES DASH'!M35);NA());"")

Does that work?

 

[Skotland]

You don't need to merge it, looks like the 0 values are being put in place by your formula if there is an error?

So just change it to;

=IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC  LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC  LOGINTIME'!$H:$H;'SALES DASH'!M35);NA());"")

Does that work?

Hmm... It still gives me 00:00:00 for the ones with zero value.

 

[Skotland]

You don't need to merge it, looks like the 0 values are being put in place by your formula if there is an error?

So just change it to;

=IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC  LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC  LOGINTIME'!$H:$H;'SALES DASH'!M35);NA());"")

Does that work?

Solved it the hard way....

=IF(IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC LOGINTIME'!$H:$H;'SALES DASH'!M35);"00:00:00");"")=0;"#N/A";IF(OR($O$1=M35;$O$1="All reps");IFERROR(SUMIFS('INPUT PC LOGINTIME'!$G:$G;'INPUT PC LOGINTIME'!$F:$F;"Available";'INPUT PC LOGINTIME'!$H:$H;'SALES DASH'!M35);"00:00:00");""))

Thanks for your input!