Average of cells that exclude conditional formats?

[AHartong]

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.

 

[David Biddulph]

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]

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.

 

[AHartong]

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.

 

[Ken]

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 <[email protected](donotspam)

 

[Ken]

This formula works for me:

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

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 <[email protected](donotspam)