Statistical functions query

[Tom]

I am trying to use some of the statistical functions on a vector of numbers
that contains occasional alphabetic data, specifically the string "FALSE".
Average(), Stdev(), Avedev(), and Median() seem to work fine, although I am
suspicious that they are not necessarily ignoring the alpha data, but
substituting a value, probably 0, for the missing data. So my first two
questions follow ...

1. Are they substituting values or are they just ignoring the alpha data;
i.e. giving the correct results?

2. If they *are* substituting values, is there anything I can do, short of
writing a script to remove the offending data, to give me an accurate
result?

Additionally, I am using the Percentile() worksheet function. That returns
a #NUM! value. I assume that it works differently than the functions I
mentioned before. My third question follows ...

3. Is there anything I can do, short of writing a script to remove the
offending data, to give me an accurate result from Percentile() with the
alpha data in the vector?


I would appreciate anybody's thoughts on this.


Thanks,

Tom

 

[Conrad Carlberg]

Hi Tom,

1. The functions are just ignoring the alpha data. Whence your suspicion
that they're substituting a numeric value?

2. While the functions are not substituting values, a useful technique in
similar situations is to use the IF function to test for a condition and to
return the array you want. Something such as this, array-entered with
Ctrl-Shift-Enter:

=AVERAGE(IF(ISNUMBER(A1:A20),A1:A20,""))

But please note that you needn't do this in the situation you describe. It's
more often used when you're specifying a different sort of condition, e.g.:

=AVERAGE(IF(A1:A20="Ishtar"),B1:B20,"")

also array-entered.

3. I suspect that the second argument to the PERCENTILE function you're
using does not fall between 0 and 1. Of course, that's just my opinion; I
could be wrong.

 

[Tom]

[posted and mailed]

Hi Tom,

1. The functions are just ignoring the alpha data. Whence your
suspicion that they're substituting a numeric value?

2. While the functions are not substituting values, a useful technique
in similar situations is to use the IF function to test for a
condition and to return the array you want. Something such as this,
array-entered with Ctrl-Shift-Enter:

=AVERAGE(IF(ISNUMBER(A1:A20),A1:A20,""))

But please note that you needn't do this in the situation you
describe. It's more often used when you're specifying a different sort
of condition, e.g.:

=AVERAGE(IF(A1:A20="Ishtar"),B1:B20,"")

also array-entered.

3. I suspect that the second argument to the PERCENTILE function
you're using does not fall between 0 and 1. Of course, that's just my
opinion; I could be wrong.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

Thanks, Conrad. You were right about the percentile. As soon as I
changed it to a decimal fraction, it yielded reasonable results.


Thanks again,

Tom