Suppress Zero On a Chart

Use #N/A instead of "" for null values.

Jerry

Bob Barnes wrote:

I have Souree Data for a Chart with some Null Values.

However, they appear as Zero on the Chart. The Tools - Options - "View" tab
unchecking "Zero values" doesn't suppress them.

Any ideas?

Jerry - Thanks for answering.

The exact situation is...I have cells adding up 2 Named Ranges fed from Access-to-Excel
automation...."=BJun+PJun". When the automation has "nothing", the Named Ranged are
Nulls. Thus "0". Yes, using "Tools - Options - "View" Tab & unchecking "Zero values"
suppresses them on the Worksheet.

But the "Source Data" displays "0" on the Chart.

Even "=IF(BMay+PMay=0,"",BMay+PMay)" shows "0" on the Chart.

Any other thoughts?

TIA - Bob

Bob -

Read Jerry's post carefully. Use NA() in place of "". "" is a text
string, interpreted numerically as zero. NA() produces #N/A, which is
ignored by the chart (though it's not a true null either). Your formula
should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bob Barnes wrote:

Jerry - Thanks for answering.

The exact situation is...I have cells adding up 2 Named Ranges fed from Access-to-Excel
automation...."=BJun+PJun". When the automation has "nothing", the Named Ranged are
Nulls. Thus "0". Yes, using "Tools - Options - "View" Tab & unchecking "Zero values"
suppresses them on the Worksheet.

But the "Source Data" displays "0" on the Chart.

Even "=IF(BMay+PMay=0,"",BMay+PMay)" shows "0" on the Chart.

Any other thoughts?

TIA - Bob

Hi, I'm interested in this too. Although using this
solution produces the desired result in the chart, it has
the cascading affect that any formulae that includes this
cell also has the outcome of #NA. Therefore these formulae
need to have a check to counter this. My solution for this
is as follows... if there is a better method please advise.

Cell E4:=IF(BMay+PMay=0,NA(),BMay+PMay)
Cell F4:=Sum(A44)+IF(ISERROR(E4),0,E4))

Cheers
Jonathan


-----Original Message-----
Bob -

Read Jerry's post carefully. Use NA() in place of "". ""
is a text
string, interpreted numerically as zero. NA() produces
#N/A, which is
ignored by the chart (though it's not a true null
either). Your formula
should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bob Barnes wrote:

Jerry - Thanks for answering.

The exact situation is...I have cells adding up 2 Named
Ranges fed from Access-to-Excel
automation...."=BJun+PJun". When the automation
has "nothing", the Named Ranged are
Nulls. Thus "0". Yes, using "Tools - Options - "View"
Tab & unchecking "Zero values"
suppresses them on the Worksheet.

But the "Source Data" displays "0" on the Chart.

Even "=IF(BMay+PMay=0,"",BMay+PMay)" shows "0" on the
Chart.

Any other thoughts?

TIA - Bob

.

Jonathan - Don't know your answer,
but I'll monitor.

Thank you, Bob

Jon - You Are Correct - Thank you.

How's it going? You can send to the Email address
you have for me.

Bob

The lack of a true null in Excel is a pain. You can either

- have two sets of the same data, one for charting (with #N/A for
nulls, and the other with 0 or "" for nulls for calculations.

- use more complex formulas for calculations. Separating every linked
cell could become unwieldy, instead use an array formula like
=SUM(IF(ISERROR(A4:E4),,A4:E4))
which must be array entered (Ctrl-Shift-Enter) to work. This will allow
any or all of the referenced cells to be linked and possibly null.

Jerry

Jonathan Parminter wrote:

Hi, I'm interested in this too. Although using this
solution produces the desired result in the chart, it has
the cascading affect that any formulae that includes this
cell also has the outcome of #NA. Therefore these formulae
need to have a check to counter this. My solution for this
is as follows... if there is a better method please advise.

Cell E4:=IF(BMay+PMay=0,NA(),BMay+PMay)
Cell F4:=Sum(A44)+IF(ISERROR(E4),0,E4))

Cheers
Jonathan



-----Original Message-----
Bob -

Read Jerry's post carefully. Use NA() in place of "". ""

is a text

string, interpreted numerically as zero. NA() produces

#N/A, which is

ignored by the chart (though it's not a true null

either). Your formula

should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon

I usually go with dual ranges, one that looks nice for the report,
another that works nice for the chart. I can usually expect to mess up a
formula if I try to get too clever. KISS.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jerry W. Lewis wrote:

The lack of a true null in Excel is a pain. You can either

- have two sets of the same data, one for charting (with #N/A for
nulls, and the other with 0 or "" for nulls for calculations.

- use more complex formulas for calculations. Separating every linked
cell could become unwieldy, instead use an array formula like
=SUM(IF(ISERROR(A4:E4),,A4:E4))
which must be array entered (Ctrl-Shift-Enter) to work. This will allow
any or all of the referenced cells to be linked and possibly null.

Jerry

Jonathan Parminter wrote:

Hi, I'm interested in this too. Although using this solution produces
the desired result in the chart, it has the cascading affect that any
formulae that includes this cell also has the outcome of #NA.
Therefore these formulae need to have a check to counter this. My
solution for this is as follows... if there is a better method please
advise.

Cell E4:=IF(BMay+PMay=0,NA(),BMay+PMay)
Cell F4:=Sum(A44)+IF(ISERROR(E4),0,E4))

Cheers
Jonathan



-----Original Message-----
Bob -

Read Jerry's post carefully. Use NA() in place of "". ""

is a text

string, interpreted numerically as zero. NA() produces

#N/A, which is

ignored by the chart (though it's not a true null

either). Your formula

should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon

Thanks Jerry,

works well :-)

Cheers
Jonathan

-----Original Message-----
The lack of a true null in Excel is a pain. You can
either

- have two sets of the same data, one for charting
(with #N/A for
nulls, and the other with 0 or "" for nulls for
calculations.

- use more complex formulas for calculations.
Separating every linked
cell could become unwieldy, instead use an array formula
like
=SUM(IF(ISERROR(A4:E4),,A4:E4))
which must be array entered (Ctrl-Shift-Enter) to work.
This will allow
any or all of the referenced cells to be linked and
possibly null.

Jerry

Jonathan Parminter wrote:

Hi, I'm interested in this too. Although using this
solution produces the desired result in the chart, it
has
the cascading affect that any formulae that includes
this
cell also has the outcome of #NA. Therefore these
formulae
need to have a check to counter this. My solution for
this
is as follows... if there is a better method please
advise.

Cell E4:=IF(BMay+PMay=0,NA(),BMay+PMay)
Cell F4:=Sum(A44)+IF(ISERROR(E4),0,E4))

Cheers
Jonathan



-----Original Message-----
Bob -

Read Jerry's post carefully. Use NA() in place
of "". ""

is a text

string, interpreted numerically as zero. NA() produces

#N/A, which is

ignored by the chart (though it's not a true null

either). Your formula

should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon

.

My personal preference is for only one copy of the data, otherwise
subsequent updates must be made in two places, but I certainly
understand the reason people might prefer the alternative.

Jerry

Jon Peltier wrote:

I usually go with dual ranges, one that looks nice for the report,
another that works nice for the chart. I can usually expect to mess up a
formula if I try to get too clever. KISS.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jerry W. Lewis wrote:

The lack of a true null in Excel is a pain. You can either

- have two sets of the same data, one for charting (with #N/A for
nulls, and the other with 0 or "" for nulls for calculations.

- use more complex formulas for calculations. Separating every
linked cell could become unwieldy, instead use an array formula like
=SUM(IF(ISERROR(A4:E4),,A4:E4))
which must be array entered (Ctrl-Shift-Enter) to work. This will
allow any or all of the referenced cells to be linked and possibly null.

Jerry

Jonathan Parminter wrote:

Hi, I'm interested in this too. Although using this solution produces
the desired result in the chart, it has the cascading affect that any
formulae that includes this cell also has the outcome of #NA.
Therefore these formulae need to have a check to counter this. My
solution for this is as follows... if there is a better method please
advise.

Cell E4:=IF(BMay+PMay=0,NA(),BMay+PMay)
Cell F4:=Sum(A44)+IF(ISERROR(E4),0,E4))

Cheers
Jonathan



-----Original Message-----
Bob -

Read Jerry's post carefully. Use NA() in place of "". ""


is a text

string, interpreted numerically as zero. NA() produces


#N/A, which is

ignored by the chart (though it's not a true null


either). Your formula

should be:

=IF(BMay+PMay=0,NA(),BMay+PMay)

- Jon