Average of cells that exclude conditional formats?
 

I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard deviation
and made simple conditional format (cells outside STDEV are in red) rules on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.

Average of cells that exclude conditional formats?
 

A formula doesn't look at the format of the cells, but only at their value.

If you know what percentage of your distribution lies outside the standard
deviation, TRIMMEAN would do the job.
--
David Biddulph

"AHartong" (donotspam) wrote in message
...
I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard
deviation
and made simple conditional format (cells outside STDEV are in red) rules
on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.

Average of cells that exclude conditional formats?
 

Actually in retrospect it wouldn't, unless the distibution is reasonably
symmetrical, as TRIMMEAN would lose the same number of samples from top and
bottom of the range.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A formula doesn't look at the format of the cells, but only at their value.

If you know what percentage of your distribution lies outside the standard
deviation, TRIMMEAN would do the job.
--
David Biddulph

"AHartong" (donotspam) wrote in message
...
I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard
deviation
and made simple conditional format (cells outside STDEV are in red) rules
on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.

Average of cells that exclude conditional formats?
 

My values are somewhat symmetrical, but there are a lot of data points well
away from teh average so losing the same number from top and bottom wouldn't
work.

"David Biddulph" wrote:

Actually in retrospect it wouldn't, unless the distibution is reasonably
symmetrical, as TRIMMEAN would lose the same number of samples from top and
bottom of the range.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A formula doesn't look at the format of the cells, but only at their value.

If you know what percentage of your distribution lies outside the standard
deviation, TRIMMEAN would do the job.
--
David Biddulph

"AHartong" (donotspam) wrote in message
...
I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard
deviation
and made simple conditional format (cells outside STDEV are in red) rules
on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.

Average of cells that exclude conditional formats?
 

It seems you are making it too complicated by addressing the
conditional format. You just need to use the formula that is used to
qualify cells for the conditional format to exclude them from the
average formula. You should be able to do that with a SUMPRODUCT or
other array formula.

Good luck.

Ken
Norfolk, Va

On Aug 6, 8:25*am, AHartong (donotspam)
wrote:
I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard deviation
and made simple conditional format (cells outside STDEV are in red) rules on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.

Average of cells that exclude conditional formats?
 

This formula works for me:

=SUMPRODUCT(--(A1:A20<upper),--(A1:A20lower),(A1:A20))/SUMPRODUCT(--
(A1:A20<upper),--(A1:A20lower))

upper is a named range that is the mean of all the data plus the
standard deviation of all the data and lower is the mean minus one
standard deviation. The data is in A1:A20.

Ken

On Aug 6, 8:25*am, AHartong (donotspam)
wrote:
I'm trying to find the average of a range of cells that exclude points
outside the standard deviation of the cells. I found the standard deviation
and made simple conditional format (cells outside STDEV are in red) rules on
the cells outside of the range, but I'm not sure how to find the average
excluding the formatted cells.