| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| problem with plotting | Excel Discussion (Misc queries) | |||
| Time calculating problem | Excel Discussion (Misc queries) | |||
| Excel 2003 xy chart - problem plotting more than 330 points | Charts and Charting in Excel | |||
| Problem with Calculating Worksheets | Excel Worksheet Functions | |||
| Problem with plotting a chart when using arrays as Values and Xvalues | Charts and Charting in Excel | |||
Linear Mode
