blank cells

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

My problem is that I want to plot a range of cells whos
values have been generated by a formula. the formula
returns "" upon certain conditions, but this means that
the chart plots these cells as zero. I can avoid this by
making the formula return #N/A (using NA()), but this
means that other formula used on these cells also return
#N/A (e.g. MAX() and MIN() functions). is there a way
around this?
many thanks
Pete
 
Hi
one workaround:
- add a helper column for your chart
- in this helper column insert the formula
=IF(B1="",NA(),B1)
and copy down
 
Pete said:
My problem is that I want to plot a range of cells whos
values have been generated by a formula. the formula
returns "" upon certain conditions, but this means that
the chart plots these cells as zero. I can avoid this by
making the formula return #N/A (using NA()), but this
means that other formula used on these cells also return
#N/A (e.g. MAX() and MIN() functions). is there a way
around this?
many thanks
Pete
Click to expand...

You could let the formulas return #N/A (for use in graphing) and create a
second table from the first (for the use of other formulas) with formulas
like
=IF(ISNA(A1),"",A1)

Alternatively, you would need to modify the other formulas to ignore #N/A
values. So, for example, instead of
=MAX(A1:A10)
you would use
=MAX(IF(NOT(ISNA(A1:A10)),A1:A10))
Note that this is an array formula, so it must be entered using
Ctrl+Shift+Enter rather than just Enter.
 
Back
Top