Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Multiple data series charting hiding zero values

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a question that I'm not sure Excel is capable of doing. I have 2 series of data, with potentially more series in one chart. These are time series based data. Here is an example:

    ColA ColB ColC ColD ColE
    X-Axis Ser1 Ser2
    2001/03 214
    2001/04 194
    2001/05 267
    2001/06 250
    2001/07 345
    2001/08 382
    2001/09 360 2
    2001/10 377 11
    2001/11 276 19
    2001/12 195 23
    2002/01 192 27
    2002/02 185 29

    I have a simple line chart and want to chart these on the same timeline without having zero values for Ser2 from 2001/03 to 2001/08. These values are based on 2 pivot tables, so the values you see above are formulas. The following are the formulas:

    ColumnA is just the text of the dates to serve as lookup values on the pivot table

    ColumnB has this formula
    =IF(ISERROR(D2),0,D2)

    ColumnC has this formula
    =IF(ISERROR(E2),"",E2)

    ColumnD has this formula
    =VLOOKUP(A2,Tables!$A$8:$B$19,2,FALSE)

    Column E has this formula
    =VLOOKUP(A2,$A$6:$B$17,2,FALSE)

    I have a "" in the formula for columnC but I still get zero values on the chart. I have also tried selecting the data from 2001/09 through 2002/02 for the data series, but then the data points are charted at the beginning of the time series and not at the appropriate x-axis coordinate.

    Any suggestions?


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Must admit I haven't read all your question, but try returnig #N/A by using NA() function in C, instead of "" . Hiding rows can also work, but is more cumbersome.

    http://www.ozgrid.com/Excel/ChartTips.htm

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You da Man!

    I didn't consider using that because I had different problems with the return of an error which shows as #N/A.

    Thank you!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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