How to exclude 0 value from a chart

[feejo]

I have the same kinda "-$" that apear apparently a name range dose not see it and take it into the graphic, so all my data are crushed in the same place cause the range is too large.

Is there a way to make the NAME range to not select the "-$"

Please Login or Register  to view this content.

[feejo]

How to exclude 0 value from a chart

[feejo]

No idea?

[Bjornar]

I think you gets no reply because most people on this forum don't understand the formulas you have used.

This is because they are in your local language and not English. This is probably an OFFSET / MATCH combination in English. Microsoft had this stupid idea that they should translate all formulas to local language in the local versions of excel .

If you don't know how to translate the formula, just upload a .zip file with your sheet or example sheet to your post. Then excel will translate the formula to english when someone with english excel opens your file(and translate separator ; to ,).

[feejo]

I think you gets no reply because most people on this forum don't understand the formulas you have used.

This is because they are in your local language and not English. This is probably an OFFSET / MATCH combination in English. Microsoft had this stupid idea that they should translate all formulas to local language in the local versions of excel .

If you don't know how to translate the formula, just upload a .zip file with your sheet or example sheet to your post. Then excel will translate the formula to english when someone with english excel opens your file(and translate separator ; to ,).

Oh your right, sorry.

Decaler = offset
NB = COUNT



Anyway I found another way.
Is to place that formula into another column
=IF(BE22=0;"NA";BE22)

Alls 0 will be changed to NA.

- Then you create the dynamic chart from it. And it will exclude all NA (that are supposed to be 0)

[feejo]

But it dose not work completely.

[vikasbhandari]

But it dose not work completely.

If your problem is to remove plotting 0s from your chart then you will need to plot errors and not "NA" in the chart! If I have understood your problem correctly, then you dont want to include the 0 in your trends...correct? Try = NA() rather than "NA". It just ignores the cell value and prepares the trend without plotting the values which are not required.... if its still not clear! Please attach a sample file you are working on!

[feejo]

I try this and it dose the same result as NA
=SI(BE22=0;"NA()";BE22)
si mean IF

Then I try in the wizard to remove the "" then
#N/A appear when there is a error.
But it keep showing in the name selection, and that name selection is the one I use in the graphic. So the graphic still the same.

[Bryan Hessey]

I try this and it dose the same result as NA
=SI(BE22=0;"NA()";BE22)
si mean IF

Then I try in the wizard to remove the "" then
#N/A appear when there is a error.
But it keep showing in the name selection, and that name selection is the one I use in the graphic. So the graphic still the same.

Hi,

try as =SI(BE22=0;#N/A;BE22)

ie, don't use quotes.


regarding the graphic, you might need to post a sample of how you are using this, perhaps Hiding the #N/A (or zero) items (by column or row) is the only way for what you need.

hth
---