Empty content after a calculation

[houghi]

I want to make a chart where I use calculated cells. However when the
cell is empty, or should be, it is treated as 0 and shows the 0, instead
of showing nothing, like a REAL empty field.

The calculation I use is (for example) =IF(A1,A1,"")
So if A1 shows something, it will show the content. If not, it shows
nothing.

To be more specific. I put numbers in A1 to A6, exept for A3, which I
leave empty. In B1 to B6 I put the calculation. When I now use B1-B6 for
a graph, I see as if A3 is 0, not as if it were empty.

How can I see to it that it is seen as empty and thus not used in a
chart. When I leave B2 empty, it does not show in the chart as it should
be.

houghi

 

[Jim Cone]

Tools | Options | Chart (tab)
Checkmark "Plot empty cells as: Not plotted (leave gaps)"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"houghi"
wrote in message
I want to make a chart where I use calculated cells. However when the
cell is empty, or should be, it is treated as 0 and shows the 0, instead
of showing nothing, like a REAL empty field.
The calculation I use is (for example) =IF(A1,A1,"")
So if A1 shows something, it will show the content. If not, it shows
nothing.
To be more specific. I put numbers in A1 to A6, exept for A3, which I
leave empty. In B1 to B6 I put the calculation. When I now use B1-B6 for
a graph, I see as if A3 is 0, not as if it were empty.
How can I see to it that it is seen as empty and thus not used in a
chart. When I leave B2 empty, it does not show in the chart as it should
be.
houghi

 

[houghi]

Tools | Options | Chart (tab)
Checkmark "Plot empty cells as: Not plotted (leave gaps)"

That does not seem to work, as the cell is not realy empty, but contains
a formula. Real empty cells are indeed ignored.

houghi

 

[Jim Cone]

Ok, then try changing your formulas...
From: =IF(A1,A1,"")
To: =IF(A1,A1,NA())

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"houghi"
wrote in message

Tools | Options | Chart (tab)
Checkmark "Plot empty cells as: Not plotted (leave gaps)"

That does not seem to work, as the cell is not realy empty, but contains
a formula. Real empty cells are indeed ignored.

houghi

 

[houghi]

Ok, then try changing your formulas...
From: =IF(A1,A1,"")
To: =IF(A1,A1,NA())

OK, that works for the chart, I just now see #N/A in the cell. What I
actualy use is:
=IF(ISERROR(GETPIVOTDATA(Bla...)),NA(),(GETPIVOTDATA(Bla...)))

I can obviously work around it by using one set of numbers for the chart
and another for the numbers, but it would be nice if I would just need
one.

What I use it for is to have 3 weeks of data, monday through friday, and
this week wil seldom be complete and holidays will need to show nothing.

houghi