IF statement in Bar Chart Data Range

**kollerrk** · 04-07-2008, 03:17 PM

In the source data for a bar chart, I would like to make it so that a series will not appear if its value is 0. So, I tried to put the following as the data range for the bar chart...

=(IF('Sheet1'!Y28=0," ",'Sheet1'!V28:Y28)), (IF('Sheet1'!Y29=0," ",'Sheet1'!V29:Y29))

And I would continue in this way to cover all of the rows of data in the chart. But, it says "reference is not valid", obviously referring to the " " in the true condition of the IF statement. Is there something I can put here to make the data range skip over a row of data if the condition is true, or do you have another suggestion for this? Thanks!

**NBVC** · 04-07-2008, 03:56 PM

I think if you make it return the #N/A error with NA(), then it should skip those rows.

Note: Your value if False are ranges... I am not sure that makes sense...

**kollerrk** · 04-09-2008, 09:34 AM

Putting the formula directly in the data range property of the chart doesn't seem to work, so I also tried your suggestion of putting the formula directly in the fields so that they would show #N/A in the fields, but the chart does not skip over them. The attached image illustrates what I am talking about. I want the bars that do not have any values to not show in the graph, and you can see how I tried the #N/A approach with one of the rows. Thanks for your help!!