[SOLVED] Trying to run an average that automatically excludes any numbers outside of a standard deviation

Trying to run an average that automatically excludes any numbers outside of a standard deviation

As stated in my question I am trying to run an average that will automatically exclude anything outside of the standard deviation and return a blank so a zero is not counted towards the average. I made a little side section for trying out nested IF(AND statements but I can't get it to work.

here is a small sample of what I was trying to do:

=AVERAGE((IF(AND(T7>=($T$10-$T$9),T7<=($T$10+$T$9)),T7,"")),(IF(AND(U7>=($T$10-$T$9),U7<=($T$10+$T$9)),U7)))

In this case T7 and U7 are the numbers I am averaging, T10 is the original average and T9 is the standard deviation. I will be having this formula average 11 total numbers. I would do it manually but I have to spread this over 1300 rows..

Solution

Here it is for an 11-cell range.

=AVERAGEIFS(T7:AD7,T7:AD7,">="&$T$10-$T$9,T7:AD7,"<="&$T$9+$T$10)

AVERAGEIFS() with the criteria being the cell must be greater than the average less the standard deviation and less than the average plus the standard deviation.