Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dates on Graphs (xl97)

    Change your formula from
    <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. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Graphs (xl97)

    Thanks for the reply,

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •