If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#N/A
When you enter a function such as median or average which
refers to a range of cells that have one or two #N/A's, it does not return a value but returns #N/A. What I would like is to have the function simply exclude NA's and calculate the function based upon the data that is available without having to continously paste and sort the data in a different area. Is there some way to do this? |
#2
|
|||
|
|||
#N/A
You can overcome that by using an array formula
=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) entered with ctrl + shift & enter or =SUMIF(A1:A10,"#N/A",A1:A10)/COUNTIF(A1:A10,"#N/A") -- Regards, Peo Sjoblom "John V." wrote in message ... When you enter a function such as median or average which refers to a range of cells that have one or two #N/A's, it does not return a value but returns #N/A. What I would like is to have the function simply exclude NA's and calculate the function based upon the data that is available without having to continously paste and sort the data in a different area. Is there some way to do this? |
Thread Tools | |
Display Modes | |
|
|