-1

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..

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
WLovejoy
  • 3
  • 2

1 Answers1

0

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.

I like Excel very much
  • 730
  • 6
  • 13