1. blanks vs. #NA (97)

I have an excel file in which some of the columns of data that I am graphing contain "na" rather than a
number. Excel plotted these at zero, which was not what I had in mind. (Not measured does not equal
zero.) Anyway, I copied the column to another location in the spreadsheet, using a formula (=NA()) to
substitute the excel null value, #NA. The points no longer plot at zero. Now excel interpolates between
them and "connects the dots." I made sure that the chart option for plotting blank values was set to
not plot. If I delete the #NA in the cell and don't replace it with anything, excel produces a correct graph
with a break in the curve. If I put the null value back, the break disappears. Even more odd: I have several,
say 4, of these null values in sequence. If I replace only the last null value with a truly blank cell, the graph
is produced correctly-- all of the other null values don't plot. But if there are no truly blank cells, excel will
interpolate. Any ideas? Anyone else run into this?

2. Re: blanks vs. #NA (97)

Not sure i'm the best person to answer this but here goes....

>>>>>contain "na" rather than a number. Excel plotted these at zero, which was not what I had in mind.
- that is because na is not the same as #n/a. it is not an error value, so excel takes the numerical value, which it works out to be zero.

>>>>>using a formula (=NA()) to substitute the excel null value, #NA. The points no longer plot at zero. Now excel interpolates between them and "connects the dots."
- you can do this either by =na() or by typing in #n/a. either way you should see the Not Available error code, #N/A. I'm not sure whether this is fully equivalent to a null value, but for graphing purposes it is the way you tell excel to interpolate missing points from your dataset. In addition to this, I believe that the #NULL! error value only applies to an intersection of two ranges that don't actually intersect.

>>>>>If I replace only the last null value with a truly blank cell, the graph is produced correctly-- all of the other null values don't plot.
- this is because excel recognises that your end value is blank - as oppposed to zero - try entering a zero here and see what happens - and tells itself not to bother interpolating to a blank value.

>>>>>Any ideas?
- I think excel is exhibiting standard behaviour here - though others might be able to give you more info.

HTH

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•