Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Series Reference If Null In Chart

    In my worksheet (Sheet2) contains dates and numbers in columns B and C, starting in row 2through 50 that are linked to data from Shee1.

    Cells B2:B50 have dates that are linked toSheet1.
    Cells C2:C50 have numbers that are linked toSheet1.

    In both of these columns (B2:B50 and C2:C50), I have an IF statements if the corresponding linked cell in Sheet1 is blank, to insert a space. IF(ISBLANK(Sheet!B2), “ “,(Sheet!B2)

    On Sheet3, I chart/graph the data from Sheet2 (Number and Date). The chart works perfect except when there is a blank cells from Sheet2, the graph line will go to zero.
    I’m looking for a formula/method so that when the SERIES reference is null, the graph line will not display in the chart.

    I know I could remove the formulas from Sheet2 that do not have a value from the linked cells and then each week copy/input the formula for that week, however, someone else will be inputting the data and I will lock all cells in both Sheet1and Sheet2 with the exceptions of Column C of Sheet1 for the user to input that week's data.

    I’ve attached a “simplified” example of the workbook where I’ve removed all data that’snot related to my question. On my attached workbook, if you will look at Sheet2/cell C44, there is no value so the graph on Sheet3 falls to zero. I would like the graph line to stop at the last value (C43 = 201).

    I would appreciate any suggestions or recommendations.

    Thank you.
    Attached Files Attached Files
    Last edited by sbdale; 2011-05-30 at 00:53.

  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
    Instead of something like:
    =IF(ISBLANK(Sheet1!C2),"",(Sheet1!C2))
    replace the null string with an NA:
    =IF(ISBLANK(Sheet1!C2),na(),Sheet1!C2)

    NA errors do not plot. Text and all other errors plot as zero.

    Steve

  3. The Following 2 Users Say Thank You to sdckapr For This Useful Post:

    RetiredGeek (2011-05-30),sbdale (2011-05-30)

Posting Permissions

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