can IF leave a cell EMPTY ??

[Clif McIrvin]

I'm attempting some simple (to me at least) data analysis and am running
into trouble with zero values in the data array (region). These zero
values (null string: "", actually) represent non-existant data that I'd
like the chart to ignore, but as the array actually contains formulas
the chart engine does not see an empty cell.

Is it possible to use IF to cause a cell to appear Empty (ie,
IsBlank(cell) == True) ?

Something like: " = if (condition, reference, Empty) "

My raw data consists of date shipped and date recieved; I have created
an array (Mon, Tue, ... Fri) that parallels my raw data where the value
in the corresponding day of week array cell is either "empty" or the
number of days in transit.

 

[Don Guillett]

Maybe?
Select your chart>tools>options>chart>interpolated

 

[Clif McIrvin]

No change. The trouble seems to be that the chart doesn't see these as
empty cells, it sees them as cells containing zero.

Thanks for responding.

--
Clif

Maybe?
Select your chart>tools>options>chart>interpolated

 

[Bernard Liengme]

=IF(condition, reference, NA())
now you will get #N/A rather than blank ('empty')
The chart engine will ignore these

But maybe you are not happy with how the table of data looks in a printed
report: just use conditional formatting to hide (make font same colour as
background) using condition formula =ISNA(cell_reference)

best wishes

 

[Shane Devenshire]

The answer depends on the type of chart you are making and exactly what you
want it to do if it is empty. You haven't shown us a sample of the data
range being used by the chart. Let's suppose it looks like this
A B
1/1/09 4
1/2/09 5
1/3/09 0
1/4/09 6
1/5/09 7

Excel can plot this data three ways by selecting the chart and choosing
Tools, Options, Chart...

You can add a formula like
=IF(X3,X3,NA())
in cell B3 of the example above, where the data is in W1:X5, and you are
using formulas in the data range, however, then the Tools, Options, Chart
options no longer apply, in that case Excel does the equivalent of
extrapolate.

If you don't want the date or data on the third row to display on the chart,
add an auto filter to the formula range and filter out the N/A in column A or
B (hide that row - choose Custom Filter from the dropdown, not equal to from
the left box and #N/A from the right box). If you are plotting dates across
the x-axis as in my example, you still have a problem, because Excel probably
has assume a Date category axis, so with the chart select choose Chart, Chart
Options, Axes, and set the Category axis to Category. If the hidden data is
still being displayed choose Tools, Options, Chart, Plot visible cells only.

If none of these ideas solve your problem, show us your data and explain
exactly what you want. (Saying you don't want to see something plotted
doesn't say what you do want Excel to do.)

 

[Clif McIrvin]

Wonderful! I can make this work for what I need.

Thanks!

--
Clif

=IF(condition, reference, NA())
now you will get #N/A rather than blank ('empty')
The chart engine will ignore these

But maybe you are not happy with how the table of data looks in a
printed report: just use conditional formatting to hide (make font
same colour as background) using condition formula
=ISNA(cell_reference)

best wishes

 

[Clif McIrvin]

Using NA() instead of "" per Bernard's and your posts gives me a workable solution. For the record, sample data looks like this:

ship arrive mon tue wed thu fri
Wed 05/20 Fri 05/29
9
Fri 05/15 Wed 05/27 12
Wed 05/06 Fri 05/15 9
Mon 04/20 Fri 05/01 11
Mon 04/06 Fri 04/17 11
Mon 03/23 Mon 03/30 7
Thu 03/19 Fri 03/27 8
Wed 03/11 Wed 03/18 7
Mon 03/02 Mon 03/09 7
Wed 02/18 Wed 02/25 7
Tue 02/10 Fri 02/20 10
Mon 02/02 Wed 02/11 9
Thu 01/29 Fri 02/06 8
Thu 01/15 Wed 01/28 13
Fri 01/09 Fri 01/23 14


where ship and arrive are formatted date values. I'm graphing the m-f grid; using an automatic category axis (The date itself is irrelevant, I'm interested only in "days in transit" information.)