Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Don't Chart ISBLANK (2003)

    In column D of the table A113 I used the formula =IF(ISBLANK(B2),"",B2/C2) hoping that the named range which the three charts reference wouldn't plot values that were blank. However, as you'll see, the charts are plotting the "0" value as though there was a "0" value present in the column D cell. They sure look blank to me, but the named ranges are returning "0" values for the purposes of the plotting the chart.

    Would I have to adjust the references of the named range and base them off of entered value in column B, but still plot Column D values?

    How would I get the charts to only plot those values which are populating as a result of the ISBLANK formula?

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't Chart ISBLANK (2003)

    1) You could use =IF(ISBLANK(B2),#N/A,B2/C2). This will work fine for the charts themselves but pose a problem for the data label.

    2) You should set the maximum value for the y-axes of the percentage charts to 1 = 100 %, not to 100 = 100000 %

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't Chart ISBLANK (2003)

    Could the OFFSET/COUNTA formulas for the named ranges be modified so that they don't return values returned by ISBLANK?

    OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$13))
    OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$13))
    OFFSET(Sheet1!$D$2,COUNTA(Sheet1!$D$2:$D$13)-MIN(4,COUNTA(Sheet1!$D$2:$D$13)),0,MIN(4,COUNTA(Sh eet1!$D$2:$D$13)),1)
    OFFSET(Sheet1!$D$1,COUNTA(Sheet1!$D$2:$D$13),0)
    OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$D$2:$D$13)-MIN(4,COUNTA(Sheet1!$D$2:$D$13)),0,MIN(4,COUNTA(Sh eet1!$D$2:$D$13)),1)

    AmyN

  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: Don't Chart ISBLANK (2003)

    If you are going to use a null string ("") as the return if B13 is blank, then instead of COUNTA use COUNT in Column D. CountA will count the null string, count will not [But the problem is that you want at least 4 to be plotted and in your dataset there will be times that only 1 or now none should be plotted....]

    Null strings are text and all text strings have a value of zero so will plot as a zero. Only a #N/A error will not be plotted. True Blanks will not be plotted, but a formula can not return a "blank" since the cell has a formula so is not blank.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't Chart ISBLANK (2003)

    Great explanation Steve! Well played. Using the null ("") as the return in combination with the following named range formulas does the trick:

    OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D$2:$D$13))
    OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$13))
    OFFSET(Sheet1!$D$2,COUNT(Sheet1!$D$2:$D$13)-MIN(4,COUNT(Sheet1!$D$2:$D$13)),0,MIN(4,COUNT(Shee t1!$D$2:$D$13)),1)
    OFFSET(Sheet1!$D$1,COUNT(Sheet1!$D$2:$D$13),0)
    OFFSET(Sheet1!$A$2,COUNT(Sheet1!$D$2:$D$13)-MIN(4,COUNT(Sheet1!$D$2:$D$13)),0,MIN(4,COUNTA(She et1!$D$2:$D$13)),1)

    Finding the correct combination of COUNT and COUNTA for the reporting period named ranges took some testing, but I got lucky quick.

    Thanks for your help.

    Amy

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't Chart ISBLANK (2003)

    Hello Amy
    Correct me if I misunderstand. You want to plot:

    <UL><LI>The data for the current month in the top chart;
    <LI>The data for the most recent n months up to 4 in the middle chart; and
    <LI>The data for the most recent n months up to 12 in the bottom chart.[/list]Then I think the attached file will do what you want.

    You can only enter data in cells B2:C13 and B16
    There are cells behind the bottom chart that are providing the re-arranged data for your charts; and the package is macro driven.

    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't Chart ISBLANK (2003)

    Strong comeback Don. You understood the requirements fine it seems. Interesting set up. I appreciate the code as well.

Posting Permissions

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