Can't use NA() instead of "" but then pb in chart with zero values

[Kami]

I have column that are created from the IF formula which returns the ""
values if the result is false. I can't use NA() instead because then, I am
using these columns for other calculations such as average.
I need to use those columns also to create charts, but my lines with "" are
showing zero values.

I read in this forum that we can use a trick solution: to have a column
using "" for the calculation and another using NA() for the graph. If I have
too many coumns, is there an easier way to solve it?
Thank,

 

[Jon Peltier]

What is "too many" columns? In my experience, it's better to use as many
columns as needed, even to the point of using separate worksheets, one for
original data, one for calculations, one for chart source data, one for the
printed report, etc. Worksheets are cheap. Your time and effort are
expensive, trying to use the same range for different purposes.

- Jon

 

[Kami]

Hi,

It is about fifty files in which I need to chart six columns.
The issue is that some of these files are growing fast (30-40 thousand
lines) and that the files become quickly heavy to work with. So each new
column will be a new 40 thousand lines column and i am afraid it won't help
the file to stay light.
But you may be right anyway, at least, everything will stay organized.

Thanks

kami

 

[Jon Peltier]

What version of Excel? If you're on 2000-2003, the issue is only disk space,
bah! In 2007, this many records can cause problems when used in calculations
or especially as a chart's source data, so performance becomes an issue.

- Jon