Forum Discussion

sure19's avatar
sure19
Copper Contributor
Jun 01, 2020

Average - Exceptions

Hi, I have this issue where i have a list of numbers example

429,528,598,643,719,802,864,31743 and I only need to remove the 31743 which is a odd number, and all other numbers are in the required range.  I tried TRIMMEAN, but that removes the high and low.  which is not right.  The right average should be 655 but when you use the TRIMMEAN i get 692.

429428.8865  
52831743  
598   
643   
719   
802   
864   
31743   
692.3978using TRIMMEAN 
655Actual average 429 to 864

 How can i just exclude the high value and not the value on the lower side.

excel

1 Reply

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 01, 2020

    sure19  you can use AVERAGEIF or AVERAGEIFS to add criteria to what you are averaging.

    For example in your case you could use:

    =AVERAGEIF(A1:A10,"<1000")

Resources