blank vs. 0 when plotting a dated timescale graph
 

Greetings!

I have a plot with dates on the x-axis and numbers on the y-axis.

I am importing database data into excel in the first tab, and
referencing that data in the second tab. The problem I run into is
that when the imported cell is blank, the reference in tab 2 displays
a 0 value. This presents a problem for my plot, which is auto-scaled
on the x-axis.

Example case (3x3 table):
x values: 03/01/07, 05/01/07, blank cell
y values: 50, 30, 20

The blank cell is for a future date that has not transpired. The y-
value is known, but the actual date realized is not, until it happens.

(1) Make a simple column chart, single series, with x values and y
values defined accordingly. You will get an auto-scaled plot showing
March, April, and May, and values of 50 and 30 plotted. This is what
I am looking for, so all is good when the data is manually enterred.

(2) Now, I make the cells in the table reference cells in Tab1 with
the values (e.g. ='Tab1'!A3). The blank cell is blank in tab1, but
displays 0 (or 1/0/1900 if cell is formatted as a date). This now
makes the plot graph a value of 20 on the date of 1/0/1900. This is
not what I'm looking for. However, if I select the cell in Tab2 and
press delete, the plot reverts to the correct view, but I lose my
reference.

I want the behavior in (1) when I'm referencing values in the other
tab, as in (2). Is this possible? Can I use an IF formula that's
something like IF('Tab1'!A3="", [leave blank / set blank], 'Tab1'!
A3)? Is there a setBlank() formula of some kind that can be used in
this fashion, whereby still maintaining the reference to the imported
cell value?

Any insight would be much appreciated.

Thanks,

Nate

blank vs. 0 when plotting a dated timescale graph
 

Use the N/A function

=IF(cell="",NA(),cell)


--
Regards,

Peo Sjoblom



wrote in message
oups.com...
Greetings!

I have a plot with dates on the x-axis and numbers on the y-axis.

I am importing database data into excel in the first tab, and
referencing that data in the second tab. The problem I run into is
that when the imported cell is blank, the reference in tab 2 displays
a 0 value. This presents a problem for my plot, which is auto-scaled
on the x-axis.

Example case (3x3 table):
x values: 03/01/07, 05/01/07, blank cell
y values: 50, 30, 20

The blank cell is for a future date that has not transpired. The y-
value is known, but the actual date realized is not, until it happens.

(1) Make a simple column chart, single series, with x values and y
values defined accordingly. You will get an auto-scaled plot showing
March, April, and May, and values of 50 and 30 plotted. This is what
I am looking for, so all is good when the data is manually enterred.

(2) Now, I make the cells in the table reference cells in Tab1 with
the values (e.g. ='Tab1'!A3). The blank cell is blank in tab1, but
displays 0 (or 1/0/1900 if cell is formatted as a date). This now
makes the plot graph a value of 20 on the date of 1/0/1900. This is
not what I'm looking for. However, if I select the cell in Tab2 and
press delete, the plot reverts to the correct view, but I lose my
reference.

I want the behavior in (1) when I'm referencing values in the other
tab, as in (2). Is this possible? Can I use an IF formula that's
something like IF('Tab1'!A3="", [leave blank / set blank], 'Tab1'!
A3)? Is there a setBlank() formula of some kind that can be used in
this fashion, whereby still maintaining the reference to the imported
cell value?

Any insight would be much appreciated.

Thanks,

Nate