How to have excelsius ignore a 0 value in excel IF formula

[Former Member]

I have some line graphs that run off of a combination of SQL data retrieved by an excel xml query and formulas in excel based on that sql data. The SQL data is basically totals for each month. I have a combo box set up to filter the data for a particular year, typically this year and I have my graphs reading the 12 rows where the combo box is dropping my sql data. The problem is I only want the graph to show data for the rows retrieved in my xml query and the formulas I have set up for all 12 rows are causing the graphs to come back with all 12 rows, showing 0 for the rows that do not have data. I set up an IF formula, with the formula returning "" for the true part and the formula result for the false part. However, excel does not put in a blank. It puts in a 0. Note that I have the ignore blank cells turned on on my line graphs.

I did some web searching and found out that excel is incapable of returning nothing in a formula result. The site recommended using NA(), which returns #N/A which excel ignores in graphs. I tried it and got an error saying Xcelsius does not support this command.

How would I set up xcelsius/excel to handle what I am trying to do?

[Former Member · Accepted solution]

If I'm reading this right, your graphs are bound to 12 data points.

You don't want data points to show up, if they're ""/0/Null/False.

Well, if you return text data, the chart will not render it. Xcelsius charts only read numbers. So, instead of a 0, you'll get a blank point.

That is instead of...

1-2-0-5-7 (the dashes represent the lines that usually connect points on a line chart)

You'll have...

1-2_____5-7 (the underline represents blank space)

<br>So for your formulas, have it return some text instead of ""/false, such as "novalue".