Problem Plotting/Calculating with #NA
 

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

Problem Plotting/Calculating with #NA
 

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in message
...
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

Problem Plotting/Calculating with #NA
 

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?

--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in message
...
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

Problem Plotting/Calculating with #NA
 

This requires a workaround. My colleague Andy Pope describes one such
workaround on his web site.

http://andypope.info/charts/brokenlines.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in message
...
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?

--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in
message
...
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

Problem Plotting/Calculating with #NA
 

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.
--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

This requires a workaround. My colleague Andy Pope describes one such
workaround on his web site.

http://andypope.info/charts/brokenlines.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in message
...
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?

--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in
message
...
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

Problem Plotting/Calculating with #NA
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in message
...
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.
--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

This requires a workaround. My colleague Andy Pope describes one such
workaround on his web site.

http://andypope.info/charts/brokenlines.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in
message
...
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?

--
Thank you,

excelBRISKbaby


"Jon Peltier" wrote:

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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"excelBRISKbaby" wrote in
message
...
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