# Thread: Dates on Graphs (xl97)

1. ## Dates on Graphs (xl97)

Can anyone help?
I've create a basic table, which has Jan to Dec, and associated data related to each month. Please see the attached graph.
I've then plotted the data on a simple line graph. The problem is that some of the associated data is linked to another table, so although the value for a particular month may be empty as it hasn't been filled in yet, (i.e. either zero or blank on the linked table), because there is a formula in the table which links it to the other datasheet, the graph attempts to show this data, resulting in 0 on the graph. So I'm left with a graph showing a couple of month's worth of data, which then tails off down to zero and stays at this level until the final month.

If there was no link to another dataset, and the cell the graph was reading from was empty, the line on the graph would just end at whatever month the data was entered up to.
How can I achieve this same result when the data in the table links to another source?

2. ## Re: Dates on Graphs (xl97)

<pre>=H3</pre>

to
<pre>=IF(ISBLANK(H3),NA(),H3)</pre>

and copy it down the column.
Excel will NOT plot NA()s as zero.

Steve

3. ## Re: Dates on Graphs (xl97)

Sorry however, but my attachment on my previous mail was not entirely correct.
The data in table 2 should be a calculated cells. So, every month shows as a value in table 2 - zero if the cell has added together empty input cells.
Therefore, using ISBLANK doesn't work, as the cell it's referencing is not actually empty, just showing zero.

Any recommendations?

4. ## Re: Dates on Graphs (xl97)

Change the formula I gave to:
<pre>=IF(H3=0,NA(),H3)</pre>

which assumes that you never will have a "truly zero" value

Or use the
<pre> =IF(ISBLANK(cell),NA(),cell)</pre>

In table 2 to not pull in the "blanks" as zero

Or replace all the blank cells in the Original source with <pre>=NA()</pre>

The #N/As will carry through all the calcs.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•