Results 1 to 2 of 2
  1. #1
    bonnb
    Guest

    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. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •