Formula returning 0 doesn't plot right

[Kartan]

i've got 1 column that's dates, and another that's values taken on those
dates. sometimes i miss a date, so the data is blank. fortunately,
when i graph these in a line graph, excel automatically ignores the
blank cells, rather than adding '0'. this makes adding trendlines
easy, as the 0's don't throw off my data.

my problem is, i also have another column which is a function of the
other column (actually, 2 other columns). when i miss a day of data,
this column reads '0.0'. now, i've figured out how to make it not
display the 0.0, but the problem is when i do a line graph of this
column v. time, it assumes that these are 0's rather than empty cells,
thus throwing off my trendline.

is there any way around this?
any help would be greatly appreciated!!!

Kartan

 

[dvt]

i've got 1 column that's dates, and another that's values taken on
those dates. sometimes i miss a date, so the data is blank.
fortunately, when i graph these in a line graph, excel automatically
ignores the blank cells, rather than adding '0'. this makes adding
trendlines
easy, as the 0's don't throw off my data.

my problem is, i also have another column which is a function of the
other column (actually, 2 other columns). when i miss a day of data,
this column reads '0.0'. now, i've figured out how to make it not
display the 0.0, but the problem is when i do a line graph of this
column v. time, it assumes that these are 0's rather than empty cells,
thus throwing off my trendline.

is there any way around this?

Change your formula to return the #N/A error if there is a zero in the
referenced cell. For example, if the data cell is B10 and the calculated
value is in C10, enter this in C10:

=if(isblank(B10),=NA(),<your formula here>)

You can use conditional formatting to hide the #N/A error if you don't like
its appearance on your spreadsheet.

Dave
dvt at psu dot edu

 

[Kartan]

Thanks! that worked perfectly. now i'm just having a little trouble
with the conditional formatting. i do "cell value is" and "equal to",
but then for the value, i don't know what to put so that it equals the
"#N/A" that i'm getting in my cells. i've tried the NA() function, the
text "N/A" and "#N/A". even NULL. no luck.

can anyone help?

 

[dvt]

Thanks! that worked perfectly. now i'm just having a little trouble
with the conditional formatting. i do "cell value is" and "equal to",
but then for the value, i don't know what to put so that it equals the
"#N/A" that i'm getting in my cells. i've tried the NA() function,
the text "N/A" and "#N/A". even NULL. no luck.

can anyone help?

Try formula =iserror(A1), where A1 is the cell reference.

Dave
dvt at psu dot edu

 

[Kartan]

thanks!