0s on my charts

cpezz

Hello - I'm creating basic bar charts and right now they look cluttered. How do I show the data values EXCEPT when that value is zero? I tried using the 0 formula on the source sheet, but it still showed up on the chart. 

sean59916

You could change the formula in the sheet to insert NULL in place of a 0. This will remove the item from the chart. 

Sean

cpezz

Thanks!

Paul Newcome

I generally use something along the lines of

 

=IF([Number Generating Formula] > 0, [Number Generating Formula])

 

This will leave the cell blank until the number is greater than zero.

 

You could even change the > to <> to get it to show negative numbers, but leave blank if zero.

Phil Wightman

Hi @Paul Newcome

Regarding the formula above to discount the zeros, can you help a little more with the construction. For example I already have a countifs formula as follows:

=COUNTIFS({Interdependencies and impacts register Range 2}, "CFCD", {Interdependencies and impacts register Range 1}, "funding")

How is this combined with your suggestion above to keep the zeros out of the chart on the dashboard? If you could write the full formula it would be a big help.

Thanks

Phil

Paul Newcome

@Phil Wightman Wherever you see "[Number Generating Formula]" in my formula above, just drop in your COUNTIFS without the leading "=".

Phil Wightman

Hi @Paul Newcome , thanks.

Can you spot why my attempt s not working?

=IF([COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"]), > 0, [COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"])

Thanks

Phil

Andrée Starå

Hi @Phil Wightman

At a glance, you have square brackets that shouldn't be in the formula.

=IF([COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"]), > 0, [COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"])

Try this.

=IF(COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", 
{New Inter-dependency Register Range 1}, "Not started"), > 0, 
COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", 
{New Inter-dependency Register Range 1}, "Not started")

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå Is correct. Sorry about that. I should have also mentioned to drop the square brackets as well. My apologies.

Phil Wightman

Hi @Andrée Starå

I used your suggested version without square brackets and I get '#INVALID REF'

Any further suggestions gratefully received.

Rgs

Paul Newcome

@Phil Wightman How exactly are you creating your cross sheet references? Are you following the steps of clicking on the "Reference Another Sheet" link in the little helper box that pops up when entering a formula, selecting the appropriate sheet, then selecting the range and inserting the reference that way?

Andrée Starå

@Phil Wightman

Did you get it working?

Jason P

Hi @Andrée Starå

I'm at a complete loss, I've look at a number of posts on this issue and have tried all by I still get a 0 in the cell, and like others it makes the dashboard to busy. Any assistance you could offer?

=if(COUNTIFS({Design - Request Register Range 2}, ($Category$86), {Request Tracker Range 4 - Request Type}, ($Category87), {Request Tracker Range 2 - Status}, "Not Started")),>0, COUNTIFS({Design - Request Register Range 2}, ($Category$86), {Request Tracker Range 4 - Request Type}, ($Category87), {Request Tracker Range 2 - Status}, "Not Started")))

Would it no be easier is 'SS added the option to not display Zero's like in Excel? these seem overly complicated formula's just to remove a 0

Cheers.

Paul Newcome

@Jason P It looks like you have some extra parenthesis and commas tucked in...

=if(COUNTIFS({Design - Request Register Range 2}, $Category$86, {Request Tracker Range 4 - Request Type}, $Category87, {Request Tracker Range 2 - Status}, "Not Started") > 0, COUNTIFS({Design - Request Register Range 2}, $Category$86, {Request Tracker Range 4 - Request Type}, $Category87, {Request Tracker Range 2 - Status}, "Not Started"))

The concept itself isn't too complicated. You just have to make sure you are dropping the right pieces into the right places.

=IF(Number_Generating_Formula > 0, Number_Generating_Formula)

Andrée Starå

@Jason P

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée 

FWPBIDev

Ugh. No answer for what should be such a fundamental and easy solution? Crazy.