"" does not seem to give a Blank cell

G

Guest

Hello!

I have what I thought was going to be a simple problem. I need to produce a
graph of the cumlative result with time; the data is outputted from access as
shown below
A B C
1 Sample_hr Flask 1 Flask 11
2 190.4 0.191433102
3 190.41
4 208.5 0.867723822
5 209.25
6 210
7 379.7
8 379.92
9 380.04 2.498789629


So I wrote the following if statement to add the cell if they contained a
value:

=IF(ISBLANK(B4)=TRUE,"",SUM(B$4:B4))

Which I copied down...so in M9 I've got

=IF(ISBLANK(B9)=TRUE,"",SUM(B$4:B9))

Then I graphed the results - and the cells with "" are graphing as 0; the
result for flask 1 at 210 hour is 0.

Also if I have another cell with =isblank(M9) I get a FALSE

Any suggestions on how to get a true blank cell???

Any help will be greatly appreciated

THANKS

Susan
 
N

Niek Otten

Hi Susan,

Use =NA() instead of ""

--
Kind regards,

Niek Otten

| Hello!
|
| I have what I thought was going to be a simple problem. I need to produce a
| graph of the cumlative result with time; the data is outputted from access as
| shown below
| A B C
| 1 Sample_hr Flask 1 Flask 11
| 2 190.4 0.191433102
| 3 190.41
| 4 208.5 0.867723822
| 5 209.25
| 6 210
| 7 379.7
| 8 379.92
| 9 380.04 2.498789629
|
|
| So I wrote the following if statement to add the cell if they contained a
| value:
|
| =IF(ISBLANK(B4)=TRUE,"",SUM(B$4:B4))
|
| Which I copied down...so in M9 I've got
|
| =IF(ISBLANK(B9)=TRUE,"",SUM(B$4:B9))
|
| Then I graphed the results - and the cells with "" are graphing as 0; the
| result for flask 1 at 210 hour is 0.
|
| Also if I have another cell with =isblank(M9) I get a FALSE
|
| Any suggestions on how to get a true blank cell???
|
| Any help will be greatly appreciated
|
| THANKS
|
| Susan
 
G

Guest

That sort of helps, but.... (Excel 2002)
On the graph the plot now assumes that a line must be drawn from (in your
example) point 2 to point 4, that's better than dropping to 0 for point 3,
but I'd rather see a break in the graph (nothing plotted between points 2 and
4).

I swear that used to work if you used a "XY (scatter)" graph (instead of
"line" graph) with the straight line option, but not today. Any ideas? I need
something like "null", a blank cells acts the way I want, but not one with
the calculation that should give a blank cell.

Glen
 
G

Guest

This looked promising...
Click the chart.
On the Tools menu, click Options, and then click the Chart tab.
Under Plot empty cells as, click the option you want.
(one option is "plot empty cell as" -not plotted)
It was the default setting, but it still plots when the formula should
return an empty cell value.
arrrrg
GG
 
J

Jon Peltier

Excel does not have a function that returns a null value or a blank or empty
cell. "" is not blank or empty, it's just a short piece of text. In some
charts, NA() simulates the "interpolate" treatment of blank cells.

If you need a gap, you either need to clear the cell of all contents, or use
a workaround like this:

http://andypope.info/charts/brokenlines.htm

- Jon
 
Top