Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default linear charting with zeros

Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.

Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?

Thx.

--
Bill


  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default linear charting with zeros

Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is interpolated
between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.

Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?

Thx.

--
Bill


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default linear charting with zeros

OK, but then the column with the formula displays #N/A instead of (what I
had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...
Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is interpolated
between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.

Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?

Thx.

--
Bill




  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default linear charting with zeros

Hi,

You could use conditional formatting to display the cells contents in
the same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of (what I
had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is interpolated
between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...

Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.

Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?

Thx.

--
Bill





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default linear charting with zeros

I also discovered that formulas on the columns containing the #N/A now don't
work. For example MAX returns #N/A.

sheesh!

--Bill

"Andy Pope" wrote in message
...
Hi,

You could use conditional formatting to display the cells contents in the
same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of (what I
had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is
interpolated between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...

Have a line chart covering many cells. Data is added to each cell
daily. The data cells use a formula. Problem is, the formula evaluates
to zero without a value in the formula's source cell. Thus, the line
chart, for future values, goes to zero.

Is there a way to have the line on the chart simply "end" without
showing zero out to the end of the data range?

Thx.

--
Bill





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info





  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default linear charting with zeros

Can you not just have to sets of the data. One for the charts with the #N/As
The other for presentation and the source for the MIN/MAX formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range),0,<range))

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
I also discovered that formulas on the columns containing the #N/A now
don't work. For example MAX returns #N/A.

sheesh!

--Bill

"Andy Pope" wrote in message
...
Hi,

You could use conditional formatting to display the cells contents in the
same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of (what
I had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather than
zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is
interpolated between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
.. .

Have a line chart covering many cells. Data is added to each cell
daily. The data cells use a formula. Problem is, the formula evaluates
to zero without a value in the formula's source cell. Thus, the line
chart, for future values, goes to zero.

Is there a way to have the line on the chart simply "end" without
showing zero out to the end of the data range?

Thx.

--
Bill





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info




  #7   Report Post  
Posted to microsoft.public.excel.charting
Jim Jim is offline
external usenet poster
 
Posts: 615
Default linear charting with zeros

How do I make a =SUM formula return NA whne there are no value to SUM yet?

I have the same problem. I want to have a line chart stop in the month where
there is no data yet.

"Andy Pope" wrote:

Can you not just have to sets of the data. One for the charts with the #N/As
The other for presentation and the source for the MIN/MAX formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range),0,<range))

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
I also discovered that formulas on the columns containing the #N/A now
don't work. For example MAX returns #N/A.

sheesh!

--Bill

"Andy Pope" wrote in message
...
Hi,

You could use conditional formatting to display the cells contents in the
same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of (what
I had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather than
zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is
interpolated between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
.. .

Have a line chart covering many cells. Data is added to each cell
daily. The data cells use a formula. Problem is, the formula evaluates
to zero without a value in the formula's source cell. Thus, the line
chart, for future values, goes to zero.

Is there a way to have the line on the chart simply "end" without
showing zero out to the end of the data range?

Thx.

--
Bill





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info





  #8   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 8,651
Default linear charting with zeros

=IF(COUNT(A1:A99)=0,NA(),SUM(A1:A99))
--
David Biddulph

Jim wrote:
How do I make a =SUM formula return NA whne there are no value to SUM
yet?

I have the same problem. I want to have a line chart stop in the
month where there is no data yet.

"Andy Pope" wrote:

Can you not just have to sets of the data. One for the charts with
the #N/As The other for presentation and the source for the MIN/MAX
formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range),0,<range))

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
I also discovered that formulas on the columns containing the #N/A
now don't work. For example MAX returns #N/A.

sheesh!

--Bill

"Andy Pope" wrote in message
...
Hi,

You could use conditional formatting to display the cells contents
in the same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of
(what I had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather
than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is
interpolated between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...

Have a line chart covering many cells. Data is added to each
cell daily. The data cells use a formula. Problem is, the
formula evaluates to zero without a value in the formula's
source cell. Thus, the line chart, for future values, goes to
zero.

Is there a way to have the line on the chart simply "end"
without showing zero out to the end of the data range?

Thx.

--
Bill




  #9   Report Post  
Posted to microsoft.public.excel.charting
Jim Jim is offline
external usenet poster
 
Posts: 615
Default linear charting with zeros

Thank you!

"David Biddulph" wrote:

=IF(COUNT(A1:A99)=0,NA(),SUM(A1:A99))
--
David Biddulph

Jim wrote:
How do I make a =SUM formula return NA whne there are no value to SUM
yet?

I have the same problem. I want to have a line chart stop in the
month where there is no data yet.

"Andy Pope" wrote:

Can you not just have to sets of the data. One for the charts with
the #N/As The other for presentation and the source for the MIN/MAX
formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range),0,<range))

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...
I also discovered that formulas on the columns containing the #N/A
now don't work. For example MAX returns #N/A.

sheesh!

--Bill

"Andy Pope" wrote in message
...
Hi,

You could use conditional formatting to display the cells contents
in the same colour as the cells fill colour.

Cheers
Andy

Bill H. wrote:
OK, but then the column with the formula displays #N/A instead of
(what I had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

"Andy Pope" wrote in message
...

Hi,

You need to adjust your formula so it evaluates to #N/A rather
than zero.

=IF( <test , <value , NA() )

Line charts will not display markers for #N/A and the line is
interpolated between valid points.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
...

Have a line chart covering many cells. Data is added to each
cell daily. The data cells use a formula. Problem is, the
formula evaluates to zero without a value in the formula's
source cell. Thus, the line chart, for future values, goes to
zero.

Is there a way to have the line on the chart simply "end"
without showing zero out to the end of the data range?

Thx.

--
Bill





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
The multiple regression tool - linear or non-linear regression? Statistical interest Excel Discussion (Misc queries) 2 February 7th 08 12:01 AM
Charting a linear equation Joseph L. Casale Charts and Charting in Excel 2 April 27th 07 07:45 PM
Charting zeros as non zeros Typical Village Idut Charts and Charting in Excel 3 February 8th 07 11:18 PM
Charting a full year without zeros Brisbane Rob Charts and Charting in Excel 3 September 24th 05 10:07 PM
Charting with zeros or DIV/0 values MarianneR Charts and Charting in Excel 1 July 9th 05 12:40 AM


All times are GMT +1. The time now is 08:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"