1. 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

2. 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. 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. 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. 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.

Amy

6. 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.

7. 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
•