Problem Plotting/Calculating with #NA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a series of formula's that I need to exclude N/A values in the
calculations. This are the equations I'm trying:

IF(ISNA(A1:A14),,AVERAGE(A1:A14))

AVERAGE(A1:A14)

As it stands now I have to highlight the formulas ->F5 ->Special
->Formulas/Errors ->OK . Delete. This wouldn't be a problem but I sort the
data so I have to do this each time I want to view the data from a different
sort.

If you could find a way to have the formula's ignore the N/A I would really
appreciate it.

excelBRISKbaby
 
Make your life easy. Have two ranges, one which retains #N/A for graphical
display, and another that converts it to "" for tabular display. Since both
ranges are linked to the same original data, both show the same values in
the cells where there aren't #N/A or "".

- Jon
 
Jon,
Thank you for your help. However the true problem I have is persisting. I
apologize. I was trying to find the answer for the workaround and not the
actual problem.

If I have a N/A within a data set (1, 2, 3, N/A, 8, ...) excel will
interpolate and not leave it blank as it does when N/A is on the edges. If I
use "" instead, then the lines go to zero. How can I chart it to leave the
area blank?
 
Jon,

Do you know of another workaround? While this will work for most
applications, I need to plot so much data that this isn't a viable option for
me.
 
I think I would copy the data to another sheet, change all errors in the
range to blank cells (actually delete the contents), and make my plot from
this data. If I had to redo it, I'd make another copy of the data, clear the
errors, and make another chart. If I had to do this frequently I'd write a
VBA procedure to handle it.

- Jon
 
Back
Top