# 0s on my charts

✭✭
edited 12/09/19

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.

«1

• ✭✭✭

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

• ✭✭

Thanks!

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭
edited 08/09/20

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

Rgs

• ✭✭✭✭✭✭

@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?

• ✭✭✭✭✭✭

Did you get it working?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭

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.

Cheers.

• ✭✭✭✭✭✭

@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)

• ✭✭✭✭✭✭

Happy to help!

Let me know if I can help with anything else!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭

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