I have a have a column with data in it...cell Z4 down to cell Z40. At the
end of each week, a new value is added in the column with a formula. I want
to have the graph not include the data when there is no value, instead of
putting the line at zero. Any thoughts?

Try something like this:

=IF(your_formula="",NA(),your_formula)

This will display #N/A instead of a blank, but this error will be
ignored by your graph. You can "hide" the #N/A by using conditional
formatting - formula is =ISNA(cell), then choose a white foreground.
(cell is the cell reference).

Hope this helps.

Pete

On Feb 21, 10:00*pm, tylermdsm
wrote:
I have a have a column with data in it...cell Z4 down to cell Z40. *At the
end of each week, a new value is added in the column with a formula. *I want
to have the graph not include the data when there is no value, instead of
putting the line at zero. *Any thoughts?

When I do that it takes care of the problem with my graph, thank you very
much. Now I'm running into another problem. I have another cell that is
averageing a few cells, and when their is an #NA in the cell, it causes the
average to also turn to an #NA, is there any way to fix this?

"Pete_UK" wrote:

Try something like this:

=IF(your_formula="",NA(),your_formula)

This will display #N/A instead of a blank, but this error will be
ignored by your graph. You can "hide" the #N/A by using conditional
formatting - formula is =ISNA(cell), then choose a white foreground.
(cell is the cell reference).

Hope this helps.

Pete

On Feb 21, 10:00 pm, tylermdsm
wrote:
I have a have a column with data in it...cell Z4 down to cell Z40. At the
end of each week, a new value is added in the column with a formula. I want
to have the graph not include the data when there is no value, instead of
putting the line at zero. Any thoughts?

You can use this array* formula instead of what you have at present:

=AVERAGE(IF(ISNA(A1:A10),FALSE,A1:A10))

* As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it
instead of the normal ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you must not type these yourself.

Obviously, change the two references A1:A10 to suit your range.

Hope this helps.

Pete

On Feb 22, 3:26*pm, tylermdsm
wrote:
When I do that it takes care of the problem with my graph, thank you very
much. *Now I'm running into another problem. *I have another cell that is
averageing a few cells, and when their is an #NA in the cell, it causes the
average to also turn to an #NA, is there any way to fix this?



"Pete_UK" wrote:
Try something like this:

=IF(your_formula="",NA(),your_formula)

This will display #N/A instead of a blank, but this error will be
ignored by your graph. You can "hide" the #N/A by using conditional
formatting - formula is =ISNA(cell), then choose a white foreground.
(cell is the cell reference).

Hope this helps.

Pete

On Feb 21, 10:00 pm, tylermdsm
wrote:
I have a have a column with data in it...cell Z4 down to cell Z40. *At the
end of each week, a new value is added in the column with a formula. *I want
to have the graph not include the data when there is no value, instead of
putting the line at zero. *Any thoughts?- Hide quoted text -

- Show quoted text -

What I usually do, to keep the formulas simpler, is to use two or more
ranges, one optimized for charting, another for subsequent calculations, and
others for whatever I need them for. perhaps a pretty table for a report.
Worksheet space is cheap, time is expensive.

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


"tylermdsm" wrote in message
...
When I do that it takes care of the problem with my graph, thank you very
much. Now I'm running into another problem. I have another cell that is
averageing a few cells, and when their is an #NA in the cell, it causes
the
average to also turn to an #NA, is there any way to fix this?

"Pete_UK" wrote:

Try something like this:

=IF(your_formula="",NA(),your_formula)

This will display #N/A instead of a blank, but this error will be
ignored by your graph. You can "hide" the #N/A by using conditional
formatting - formula is =ISNA(cell), then choose a white foreground.
(cell is the cell reference).

Hope this helps.

Pete

On Feb 21, 10:00 pm, tylermdsm
wrote:
I have a have a column with data in it...cell Z4 down to cell Z40. At
the
end of each week, a new value is added in the column with a formula. I
want
to have the graph not include the data when there is no value, instead
of
putting the line at zero. Any thoughts?

Have the formula return NA() when there is not value:

IF(LEN(K40)=0,NA(),K40)

This gives you #N/A in the sheet, but isn't plotted as a point in the line
chart.

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


"tylermdsm" wrote in message
...
I have a have a column with data in it...cell Z4 down to cell Z40. At the
end of each week, a new value is added in the column with a formula. I
want
to have the graph not include the data when there is no value, instead of
putting the line at zero. Any thoughts?