Formula returning 0 doesn't plot right

  • Thread starter Thread starter Kartan
  • Start date Start date
K

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
 
Kartan said:
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?
Click to expand...

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
 
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?
 
Kartan said:
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?
Click to expand...

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

Dave
dvt at psu dot edu
 
Back
Top