Empty strings appear as zeros

[Paul Martin]

I have some formulas that return empty strings if certain conditions
exist. The intention is that charts NOT show data in these cases.
Yet, they show up as zeros. I've read on here about the use of NA(),
and that works fine, but then if I have another formula that looks at
these values for the maximum value (of the data series, to
automatically adjust the max scale), it won't work because of the NA()
error.

What I would like is to have the empty string values NOT appear in the
chart, and I'm wondering if there's another solution other than
forcing the use of NA()??

Any suggestions appreciated.


Paul Martin
Melbourne, Australia

 

[Paul Martin]

I should also mention that I've seen the posts about setting the Chart
Options to plot empty cells as 'Not plotted' (which has no effect) and
'Interpolated' (which is not enabled), so this seems to be no help.

 

[Andy Pope]

Hi,

That option will only apply to cells that are truly empty.

You can either use another column with the variation of the formula
returning "" so the maximum scale value can be determined.
Or perhaps modify your formula to determine the maximum value. The array
formula, commit using CTRL+SHIFT+ENTER, will return the maximum value of the
cells B2:B6 ignoring any that contain #N/A

=MAX(IF(ISNA(B2:B6),"",B2:B6))

Cheers
Andy

 

[Jon Peltier]

My favorite technique is to have one range for tabular display, one range
for the chart source, and one range for subsequent calculations. They all
link to the original data, so they're all "correct", but each is optimized
for its own purpose, and therefore each has variations on the formulas used,
to help in this modification.

Cells are cheap. Worksheets are cheap. Your time and frustration are not
cheap at all.

- Jon

 

[Paul Martin]

While awaiting a reply, I had already employed Jon Peltier's method of
an additional set of calculations, which is working fine, though I was
hoping there was a solution with less redundancy. As much as I don't
want to leave array formulas in the hands of users, Andy Pope's and
Dave Curtis' suggestions look quite appealling as it will remove the
need for a separate sheet and second data set. Both solutions are
equally valid, depending on preference.

Thanks guys for each of your replies.