How do I convert functions that are zero into a null ?
 

I have a sales worksheet that totals monthly from January thru December.

I plot the summary sales by month in a simple line chart.

The problem is that since the year is not completed, the line chart drops
off to zero for the month that total zero which makes it look like a sales
decline. I would like to make it just stop plotting at the last month with
sales.

I can do this manually by deleting the totals for each month but I would
prefer to use some kind of an "IF" statement and a "NULL" value but am unable
to find the correct mix.

Any ideas ? Thanks In advance. Nick.

To get the equivalent of a null value in a chart you can use

=IF(cell=0,NA(),cell)



Regards,

Peo Sjoblom

"Nick" wrote:

I have a sales worksheet that totals monthly from January thru December.

I plot the summary sales by month in a simple line chart.

The problem is that since the year is not completed, the line chart drops
off to zero for the month that total zero which makes it look like a sales
decline. I would like to make it just stop plotting at the last month with
sales.

I can do this manually by deleting the totals for each month but I would
prefer to use some kind of an "IF" statement and a "NULL" value but am unable
to find the correct mix.

Any ideas ? Thanks In advance. Nick.

This worked great

Also, how can I test for a cell with an NA or #N/A value with an IF function ?

"Peo Sjoblom" wrote:

To get the equivalent of a null value in a chart you can use

=IF(cell=0,NA(),cell)



Regards,

Peo Sjoblom

"Nick" wrote:

I have a sales worksheet that totals monthly from January thru December.

I plot the summary sales by month in a simple line chart.

The problem is that since the year is not completed, the line chart drops
off to zero for the month that total zero which makes it look like a sales
decline. I would like to make it just stop plotting at the last month with
sales.

I can do this manually by deleting the totals for each month but I would
prefer to use some kind of an "IF" statement and a "NULL" value but am unable
to find the correct mix.

Any ideas ? Thanks In advance. Nick.

Once again, thanks for your fast and accurate answer

In regards to my reply about the ISNA() function, please ignore ... I was
using the incorrect syntax.

Best Regards,
Nick

"Peo Sjoblom" wrote:

To get the equivalent of a null value in a chart you can use

=IF(cell=0,NA(),cell)



Regards,

Peo Sjoblom

"Nick" wrote:

I have a sales worksheet that totals monthly from January thru December.

I plot the summary sales by month in a simple line chart.

The problem is that since the year is not completed, the line chart drops
off to zero for the month that total zero which makes it look like a sales
decline. I would like to make it just stop plotting at the last month with
sales.

I can do this manually by deleting the totals for each month but I would
prefer to use some kind of an "IF" statement and a "NULL" value but am unable
to find the correct mix.

Any ideas ? Thanks In advance. Nick.

Nick

Check out a dynamic range for the series that will stop at last data and
ignore "" cells.

Tushar Mehta has methods.

http://www.tushar-mehta.com/excel/ne...rts/index.html

Also Jon Peltier's site.

http://peltiertech.com/Excel/Charts/index.html#hdrDyno


Gord Dibben Excel MVP

On Wed, 11 May 2005 14:08:05 -0700, "Nick" <Nick @discussions.microsoft.com
wrote:

I have a sales worksheet that totals monthly from January thru December.

I plot the summary sales by month in a simple line chart.

The problem is that since the year is not completed, the line chart drops
off to zero for the month that total zero which makes it look like a sales
decline. I would like to make it just stop plotting at the last month with
sales.

I can do this manually by deleting the totals for each month but I would
prefer to use some kind of an "IF" statement and a "NULL" value but am unable
to find the correct mix.

Any ideas ? Thanks In advance. Nick.