Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average (exclude 1 min & 1 max Value) | Excel Discussion (Misc queries) | |||
Average non-adjacent cells & exclude zeros | Excel Discussion (Misc queries) | |||
conditional formats for cells | Excel Worksheet Functions | |||
conditional formats affect other cells | Excel Discussion (Misc queries) | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions |