Thank you, that's very kind of you! I appreciate it.
Suppose I have my list of mixed numbers and #N/As in column D, and I have produced them through some more complicated function or another. Would the averaging function then be something like
=SUMIF(D2:D6,ISNUMBER)/COUNTIF(D2:D6,ISNUMBER)
or similar? *checks* No, that doesn't work. Can you see why I would expect it to work, though?
OK, let's simplify. Suppose I have a list that is just
6 2 -3 #N/A 4 7 #N/A 2
and I want to find the sum of the numbers therein. =SUMIF(A1:A8,ISNUMBER) gives zero. =SUMIF(A1:A8,">0") gives a numerical answer, but gives me the wrong answer because it doesn't take the -3 into account. =SUMIF(A1:A8,"<>0") gives #N/A. =SUMIF(A1:A8,"!=0") gives zero. =SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0") works, but is ungainly.
By extension, we can get the average of the numbers in the list from =(SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0"))/(COUNTIF(A1:A8,">0")+COUNTIF(A1:A8,"<0")) - but is there a neater way of doing it?
Next question. I would like to run the PERCENTILE function on a range of data, like the mixture of numbers and #N/As above, to work out what the (e.g.) 74th percentile of the list is. Now these #N/A cells don't actually have to be #N/As, but they need to be things that clearly aren't going to be treated as numbers by the PERCENTILE funtion, aren't going to make the PERCENTILE function choke and crucially can be produced as the result of another function such as IF. Is there is a cunning way to do that, please?
Re: did someone say Excel?
Suppose I have my list of mixed numbers and #N/As in column D, and I have produced them through some more complicated function or another. Would the averaging function then be something like
=SUMIF(D2:D6,ISNUMBER)/COUNTIF(D2:D6,ISNUMBER)
or similar? *checks* No, that doesn't work. Can you see why I would expect it to work, though?
OK, let's simplify. Suppose I have a list that is just
6
2
-3
#N/A
4
7
#N/A
2
and I want to find the sum of the numbers therein.
=SUMIF(A1:A8,ISNUMBER) gives zero.
=SUMIF(A1:A8,">0") gives a numerical answer, but gives me the wrong answer because it doesn't take the -3 into account.
=SUMIF(A1:A8,"<>0") gives #N/A.
=SUMIF(A1:A8,"!=0") gives zero.
=SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0") works, but is ungainly.
By extension, we can get the average of the numbers in the list from =(SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0"))/(COUNTIF(A1:A8,">0")+COUNTIF(A1:A8,"<0")) - but is there a neater way of doing it?
Next question. I would like to run the PERCENTILE function on a range of data, like the mixture of numbers and #N/As above, to work out what the (e.g.) 74th percentile of the list is. Now these #N/A cells don't actually have to be #N/As, but they need to be things that clearly aren't going to be treated as numbers by the PERCENTILE funtion, aren't going to make the PERCENTILE function choke and crucially can be produced as the result of another function such as IF. Is there is a cunning way to do that, please?