Solution for charting IF statements that return blank values

[peterwe]

Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a1>0,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:

From the top file menu's select Insert > Name > Define.

Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<>"",COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!

 

[Jon Peltier]

This is the preferred approach, when the "blanks" occur at either end of the
range. When they occur in the middle, you have fewer options.

Another approach is to have two data ranges, one for viewing tabular output,
and one laid out for efficient charting. The former links to the original
data with "", the latter with NA(). The benefits of this include flexibility
in layout and format of the tabular output, without worrying about getting
it to plot the way you want; not requiring CF to hide errors in the table;
not hiding the errors in the chart data, so you can more readily debug data
issues; and more. The two data regions can be linked to each other, or
preferably to a third main data sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a1>0,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:

From the top file menu's select Insert > Name > Define.

Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<>"",COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!