Stop chart lines going to zero

Chart lines drop to zero on last point unless cell is highlighted and delete
key pressed, which is no good if there is a formulae in the cell. Anybody
know how to cure this ??

In article , =?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted and delete
key pressed, which is no good if there is a formulae in the cell. Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article , =?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted and delete
key pressed, which is no good if there is a formulae in the cell. Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Good guess, though, considering there was no formula in the original post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tom,

I had this same problem when I tested the suggested change in a formula I was
using. The problem was I only changed one cell as a test and the chart line
still dropped to zero due to the subsequent cells having "" instead of NA().

You have to apply this formula to all cells in the column otherwise the first
cell with "" instead of NA() will cause the chart line to drop to zero.

JC

On Fri, 3 Mar 2006 18:55:27 -0800, Tom H wrote:

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Another point: the NA() only prevents Line chart and XY chart points from
displaying. Other chart types will display a zero value for NA(). For a
column or bar chart it usually doesn't matter, since a zero value usually
just doesn't show because it's the minimum height of the axis (although
you'll get a #N/A data label if you are showing values). But an area chart
will display this drop from the last nonzero point to zero.

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

"JC" wrote in message
...
Tom,

I had this same problem when I tested the suggested change in a formula I
was
using. The problem was I only changed one cell as a test and the chart
line
still dropped to zero due to the subsequent cells having "" instead of
NA().

You have to apply this formula to all cells in the column otherwise the
first
cell with "" instead of NA() will cause the chart line to drop to zero.

JC

On Fri, 3 Mar 2006 18:55:27 -0800, Tom H
wrote:

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original
post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted
and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Unfortunately this still doesn't work, even with formulae applied to all
cells, perhaps there is a way with conditional formating to switch the line
chart off ?

"Jon Peltier" wrote:

Another point: the NA() only prevents Line chart and XY chart points from
displaying. Other chart types will display a zero value for NA(). For a
column or bar chart it usually doesn't matter, since a zero value usually
just doesn't show because it's the minimum height of the axis (although
you'll get a #N/A data label if you are showing values). But an area chart
will display this drop from the last nonzero point to zero.

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

"JC" wrote in message
...
Tom,

I had this same problem when I tested the suggested change in a formula I
was
using. The problem was I only changed one cell as a test and the chart
line
still dropped to zero due to the subsequent cells having "" instead of
NA().

You have to apply this formula to all cells in the column otherwise the
first
cell with "" instead of NA() will cause the chart line to drop to zero.

JC

On Fri, 3 Mar 2006 18:55:27 -0800, Tom H
wrote:

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original
post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted
and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Okay, I suspect something wasn't adequately communicated. What's the data
look like? What range is it in? What does the series formula look like. What
does the formula in the worksheet look like? What cells is it in? What do
the results of the formula look like.

If you can't get this to work, you won't be able to get any conditional
formatting to work in the chart either, because conditional formatting in a
chart is based on similar formulas incorporating NA().

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

"Tom H" wrote in message
...
Unfortunately this still doesn't work, even with formulae applied to all
cells, perhaps there is a way with conditional formating to switch the
line
chart off ?

"Jon Peltier" wrote:

Another point: the NA() only prevents Line chart and XY chart points from
displaying. Other chart types will display a zero value for NA(). For a
column or bar chart it usually doesn't matter, since a zero value usually
just doesn't show because it's the minimum height of the axis (although
you'll get a #N/A data label if you are showing values). But an area
chart
will display this drop from the last nonzero point to zero.

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

"JC" wrote in message
...
Tom,

I had this same problem when I tested the suggested change in a formula
I
was
using. The problem was I only changed one cell as a test and the chart
line
still dropped to zero due to the subsequent cells having "" instead of
NA().

You have to apply this formula to all cells in the column otherwise the
first
cell with "" instead of NA() will cause the chart line to drop to zero.

JC

On Fri, 3 Mar 2006 18:55:27 -0800, Tom H

wrote:

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original
post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted
and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Jon,

Went back through all the formulaes upsteam of the empty cells and found one
that still had the old formulae in - Now works fine on the graph, but #N/A
shows in the data cells that don't have a result in. Is there a way to stop
the #N/A from showing, as this is also interfering with other formulaes -
Thanks.

"Jon Peltier" wrote:

Okay, I suspect something wasn't adequately communicated. What's the data
look like? What range is it in? What does the series formula look like. What
does the formula in the worksheet look like? What cells is it in? What do
the results of the formula look like.

If you can't get this to work, you won't be able to get any conditional
formatting to work in the chart either, because conditional formatting in a
chart is based on similar formulas incorporating NA().

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

"Tom H" wrote in message
...
Unfortunately this still doesn't work, even with formulae applied to all
cells, perhaps there is a way with conditional formating to switch the
line
chart off ?

"Jon Peltier" wrote:

Another point: the NA() only prevents Line chart and XY chart points from
displaying. Other chart types will display a zero value for NA(). For a
column or bar chart it usually doesn't matter, since a zero value usually
just doesn't show because it's the minimum height of the axis (although
you'll get a #N/A data label if you are showing values). But an area
chart
will display this drop from the last nonzero point to zero.

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

"JC" wrote in message
...
Tom,

I had this same problem when I tested the suggested change in a formula
I
was
using. The problem was I only changed one cell as a test and the chart
line
still dropped to zero due to the subsequent cells having "" instead of
NA().

You have to apply this formula to all cells in the column otherwise the
first
cell with "" instead of NA() will cause the chart line to drop to zero.

JC

On Fri, 3 Mar 2006 18:55:27 -0800, Tom H

wrote:

Jon, Have tried this again and still doesn't work.

Regards Tom H

"Jon Peltier" wrote:

Good guess, though, considering there was no formula in the original
post.
Try this:

=IF(H110="",NA(),SUM(K110/H110))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tom H" wrote in message
...
Thanks for the quick reply Tushar, but this didn't fix the problem.
The formulae I am using is:- =IF(H110="","",SUM(K110/H110))

Regards Tom H


"Tushar Mehta" wrote:

In article ,
=?Utf-8?B?
VG9tIEg=?= Tom says...
Chart lines drop to zero on last point unless cell is highlighted
and
delete
key pressed, which is no good if there is a formulae in the cell.
Anybody
know how to cure this ??


Replace the "" in the formula with NA()

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions