software

Hi,

I have a range of cells (100 ), which should all have a value of around
3600, so the average should also be around 3600 (minimum should not be lower
then 300, maximum shouldn't be higher than 10000). But sometimes some cells
contain values of -2000000 or /-2 or 2000000. So when I calculate the
average (using the AVERAGE Function) I get abnormal results.

Is there a function that can automaticly reconize the excessive values and
exclude them from the Average calculation.

Thanks,

Bart
Bart,

Array enter using Ctrl-Shift-Enter:

=AVERAGE(IF((A1:A100gt;300)*(A1:A100lt;10000),A1:A100) )

Change range to match your actual values.

HTH,
Bernie
MS Excel MVPquot;Bart Steurquot; gt; wrote in message ...
gt; Hi,
gt;
gt; I have a range of cells (100 ), which should all have a value of around 3600, so the average
gt; should also be around 3600 (minimum should not be lower then 300, maximum shouldn't be higher than
gt; 10000). But sometimes some cells contain values of -2000000 or /-2 or 2000000. So when I
gt; calculate the average (using the AVERAGE Function) I get abnormal results.
gt;
gt; Is there a function that can automaticly reconize the excessive values and exclude them from the
gt; Average calculation.
gt;
gt; Thanks,
gt;
gt; Bart
gt;
This works for me
=SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000),A1:A100)/SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Bart Steurquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I have a range of cells (100 ), which should all have a value of around
gt; 3600, so the average should also be around 3600 (minimum should not be
gt; lower then 300, maximum shouldn't be higher than 10000). But sometimes
gt; some cells contain values of -2000000 or /-2 or 2000000. So when I
gt; calculate the average (using the AVERAGE Function) I get abnormal results.
gt;
gt; Is there a function that can automaticly reconize the excessive values and
gt; exclude them from the Average calculation.
gt;
gt; Thanks,
gt;
gt; Bart
gt;
There is no statistical/analitical function to do this. A function that
recognizes high or low values compared to the rest and excludes them?

Bartquot;Bernard Liengmequot; gt; wrote in message
...
gt; This works for me
gt; =SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000),A1:A100)/SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000))
gt;
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Bart Steurquot; gt; wrote in message
gt; ...
gt;gt; Hi,
gt;gt;
gt;gt; I have a range of cells (100 ), which should all have a value of around
gt;gt; 3600, so the average should also be around 3600 (minimum should not be
gt;gt; lower then 300, maximum shouldn't be higher than 10000). But sometimes
gt;gt; some cells contain values of -2000000 or /-2 or 2000000. So when I
gt;gt; calculate the average (using the AVERAGE Function) I get abnormal
gt;gt; results.
gt;gt;
gt;gt; Is there a function that can automaticly reconize the excessive values
gt;gt; and exclude them from the Average calculation.
gt;gt;
gt;gt; Thanks,
gt;gt;
gt;gt; Bart
gt;gt;
gt;
gt;
No, there is no built-in function that knows about 'outliers'

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Bart Steurquot; gt; wrote in message
...
gt; There is no statistical/analitical function to do this. A function that
gt; recognizes high or low values compared to the rest and excludes them?
gt;
gt; Bart
gt;
gt;
gt; quot;Bernard Liengmequot; gt; wrote in message
gt; ...
gt;gt; This works for me
gt;gt; =SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000),A1:A100)/SUMPRODUCT(--(A1:A100gt;=300),--(A1:A100lt;=10000))
gt;gt;
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt; quot;Bart Steurquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi,
gt;gt;gt;
gt;gt;gt; I have a range of cells (100 ), which should all have a value of around
gt;gt;gt; 3600, so the average should also be around 3600 (minimum should not be
gt;gt;gt; lower then 300, maximum shouldn't be higher than 10000). But sometimes
gt;gt;gt; some cells contain values of -2000000 or /-2 or 2000000. So when I
gt;gt;gt; calculate the average (using the AVERAGE Function) I get abnormal
gt;gt;gt; results.
gt;gt;gt;
gt;gt;gt; Is there a function that can automaticly reconize the excessive values
gt;gt;gt; and exclude them from the Average calculation.
gt;gt;gt;
gt;gt;gt; Thanks,
gt;gt;gt;
gt;gt;gt; Bart
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
On Fri, 30 Dec 2005 12:20:17 0100, quot;Bart Steurquot; gt; wrote:

gt;There is no statistical/analitical function to do this. A function that
gt;recognizes high or low values compared to the rest and excludes them?
gt;
gt;Bart
gt;
gt;

Take a look at HELP for the TRIMMEAN function. This can help you eliminate
outliers.--ron