Home |
Search |
Today's Posts |
#1
|
|||
|
|||
character that is ignored when averaging
Hello:
I have several cells to average. In some of those cells, I have invalid values, so I have an if statement that replaces the cells with #N/A is the values are invalid. This is good because it allows me to graph the values and the #N/A are ignored (in an X-Y scatter plot) However, if I try to average and the range includes one of those cells, I get a value of #N/A. I want to be able to average those values ignoring the #N/A. If I replace the #N/A with '' or something similar, then the average works but the graph doesn't (it gets turned into a line plot). If there something tat satisfies both requirements? thanks guillermo |
#2
|
|||
|
|||
One way:
=SUMIF(A:A,"<#N/A")/COUNT(A:A) HTH Jason Atlanta, GA -----Original Message----- Hello: I have several cells to average. In some of those cells, I have invalid values, so I have an if statement that replaces the cells with #N/A is the values are invalid. This is good because it allows me to graph the values and the #N/A are ignored (in an X-Y scatter plot) However, if I try to average and the range includes one of those cells, I get a value of #N/A. I want to be able to average those values ignoring the #N/A. If I replace the #N/A with '' or something similar, then the average works but the graph doesn't (it gets turned into a line plot). If there something tat satisfies both requirements? thanks guillermo . |
#3
|
|||
|
|||
thanks
guillermo "Jason Morin" wrote in message ... One way: =SUMIF(A:A,"<#N/A")/COUNT(A:A) HTH Jason Atlanta, GA -----Original Message----- Hello: I have several cells to average. In some of those cells, I have invalid values, so I have an if statement that replaces the cells with #N/A is the values are invalid. This is good because it allows me to graph the values and the #N/A are ignored (in an X-Y scatter plot) However, if I try to average and the range includes one of those cells, I get a value of #N/A. I want to be able to average those values ignoring the #N/A. If I replace the #N/A with '' or something similar, then the average works but the graph doesn't (it gets turned into a line plot). If there something tat satisfies both requirements? thanks guillermo . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a column in worksheet with a character change | Excel Worksheet Functions | |||
How can I combine multiple characters into a single character? | Excel Discussion (Misc queries) | |||
Searching a cell for a certain character. | Excel Worksheet Functions | |||
Removing ' character from cells | Excel Discussion (Misc queries) | |||
Test for Single Character That is in an Array | Excel Worksheet Functions |