Ignore #'¡DIV/0! Values in both axes of a bar

问题:

I have this bar chart in excel :

As you see, if there is #'¡DIV/0!, the values are 0. What I really want is that the bar chart just shows the numeric values. In this case. just the first three values would have to be visualized. I tried the formula IF.ERROR(FORMULA,"") but the result was the same,

So, How Can I do that?

回答1:

You need dynamic ranges for the chart, or filter the data source to hide the rows with the error. Excel charts will not include data that is hidden with a filter.

To apply dynamic ranges, create two range names with the following formulas:

chtLabels   =OFFSET(chtValues,0,-1)
chtValues   =Sheet1!$B$12:INDEX(Sheet1!$B:$B,MATCH(99^99,Sheet1!$B:$B,1))

Adjust the chtValues range name to start in the first cell with a number in column B instead of B12 of the example.

Edit the column chart series values and enter the range name, preceded by the sheet name, like

=Sheet1!chtValues

Edit the Horizontal axis labels and replace the range with the dynamic named range

=Sheet1!chtLabels

When you exit the dialog, Excel will replace the sheet name with the file name. The result will be similar to this:

Note how the series formula shows the range names instead of the cell ranges.

回答2:

=IFERROR(value, value_if_error) where value_if_error should equal 0 or "" or whatever you want