Eliminating "0" Chart Points on Cells that are "Blank"

jmenzel30

New Member
Hello,

I'm having an issue that I believe the most difficult part is asking the question. So I'm going to try to do pictures. However as a quick rundown, I have 4 tables. General structure of tables is:

 January Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Total 2022 \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ -

So there are 4 separate rows setup like that. Each Cell is linked to an external workbook. Each data set in the table is linked to a different external source (This is because each company has their own report). Then there is a 5th sheet that tracks a total of the 4 sheets. The current formula is as follows: =IFERROR(IF(SUM(B\$9+B\$20+B\$29+B\$36)<>"",(B\$9+B\$20+B\$29+B\$36),""),"") which looks great in the table. But if you look at the chart below it is being plotted on zero. How can I make that dynamic and not plot zero? I think it's probably in the formula because so far I have tried going to select data and hide zero's/blanks, I've done that setting in the options>advanced>display>don't show zero's. Not having any luck. Anybody have better ideas?

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
you have strings(=text) in your cells, try SUM(B\$9,B\$20,B\$29,B\$36).
The formula 'll skip the strings.
Do you have values in that 5t sheet ?

you have strings(=text) in your cells, try SUM(B\$9,B\$20,B\$29,B\$36).
The formula 'll skip the strings.
Do you have values in that 5t sheet ?
values will be in the fifth sheet at month end when the values for January are filled in for the other 4 tables. So there is a formula in the cell. But no displayed value until the numbers are placed on the sheet. Does that make sense?

Excel Formula:
``=IF(SUM(B\$9,B\$20,B\$29,B\$36)<>0,sum(B\$9,b\$20,B\$29,B\$36),NA())``

Excel Formula:
``=IF(SUM(B\$9,B\$20,B\$29,B\$36)<>0,sum(B\$9,b\$20,B\$29,B\$36),NA())``
So when I use that formula, I get #VALUE in the cell, and it still plots a 0 on the Chart. I've tried formatting all cells to "number" and that doesn't seem to do it either. So that's why I built in the iferror, but its still plotting on my chart.

So when I use that formula, I get #VALUE in the cell, and it still plots a 0 on the Chart. I've tried formatting all cells to "number" and that doesn't seem to do it either. So that's why I built in the iferror, but its still plotting on my chart.
I have also tried Ctrl+Shift+Enter to get rid of the #Value and that doesn't seem to work either.

as long as the month isn't finished, the sum is 0, i assume and a 0 is replaced by NA() (= an error).
The IfERROR may not replace that value, so delete that part of the formula.
What values have the cells B\$9,B\$20,B\$29 and B\$36 now ?

as long as the month isn't finished, the sum is 0, i assume and a 0 is replaced by NA() (= an error).
The IfERROR may not replace that value, so delete that part of the formula.
What values have the cells B\$9,B\$20,B\$29 and B\$36 now ?
I did indeed take that part out. Currently in B\$9,B\$20,B\$29 and B\$36 they display as a blank cell, but they have a link to a cell on another worksheet. So I think that's creating the problem, but Idk how to fix it. My only thought is if I try something with the "isblank" Function.

Replies
6
Views
134
Replies
0
Views
357
Replies
1
Views
181
Replies
3
Views
102
Replies
4
Views
206

1,203,485
Messages
6,055,686
Members
444,807
Latest member
RustyExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back