Avoiding empty cells showing as zero in line chart without na()

[ibjiba]

I'm using a line graph to show trends in a multi-question
questionnaire. Like many else (I've done a lot of searching but haven't
found a suitable solution) my line graph jumps to zero for the empty
cells (currently defined as ""). I understand I could just change this
to na() instead but then my average() calculation stops working. I have
a lot of data so making two separate data-series is not feasible.

Can anyone help me with a suggestion here? Thanks!

 

[Jon Peltier]

It's not feasible to have parallel data ranges? It's more feasible to spend
lots of time trying to workaround the problem?

Here are a couple suggestions:

1. Use conditional formatting to hide the #N/A errors and array formulas
like {=AVERAGE(IF(ISNA(range),,range))} for the calculations. This approach
is fine for smaller data sets, or if formatting isn't too critical.

2. Have two sets of data, one for tabular presentation (two for tabular
presentation if you want one optimized for onscreen and another for
printout) and one for charting. With appropriate formulas, they will remain
linked, and reflect the same information. You can put them on separate
regions of the same worksheet or on separate worksheets. It would take a few
extra minutes to set up initially, but it will save you probably hours of
frustration later. I prefer this approach for larger data sets, and cases
where formatting is important.

- Jon

 

[Andy Pope]

Hi,

You could change the average formula such that NA() can be used,

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

Array formula so commit with CTRL+SHIFT+ENTER

Cheers
Andy