엑셀] 최고, 최저 점수를 제외하고 산술평균 계산하기

최댓값과 최솟값을 제외하고 산술평균 계산하기

(아이구.. 어색해라. 최대값, 최소값으로 수십년을 쓴거 같은데 최댓값과 최솟값이 맞는 표기라니까...쩝.)

심사위원의 점수 중에서 최고점과 최저점수를 제외한 산술평균으로 점수를 산출하는 방식이 피겨스케이팅에만 있는건 아니다. ^^;; 

실무에서 협상에 의한 계약같은 업무를 진행하다보면 정성적 평가를 할 때 제안요청서(RFP)에 "정성적 평가점수의 집계는 평가항목별 최고 및 최저 점수를 제외한 나머지 점수의 산술평균으로 산정하고..."라고 한다. 

그러면 담당자는 저걸 빨리 계산해야 하는 일거리가 주어지는거고.. ^^;;

이번 블로그 글에서는 (최대값과 최소값) 최댓값과 최솟값을 제외하고 산술평균 내는 방법에 대해 알아보자. 

첫번째 방법 : AVERAGEIFS함수

일부 엑셀 교재에서 최대, 최솟값을 제외한 평균을 내는 방법으로 소개되고 있던데 일단 수식은 다음과 같다. 

=AVERAGEIFS(평가범위, 

            평가범위,"<"&MAX(평가범위),

            평가범위,">"&MIN(평가범위))

이 함수를 사용해 보면 계산을 잘 한다. 

단. 최고점수나 최저점수가 동점인 경우 결과값이 다르게 나온다. 

평가표를 averageifs함수로 작성했다가 테스트하면서 발견하게 되서 정말 놀랐던 기억이... ㅋ

두번째 방법 : TRIMMEAN함수

TRIMMEAN함수는 평균을 구할 때 인수로 주어진 퍼센트의 데이터만큼 제외하고 평균을 구하는 함수이다. 

첫번째 인수로 평균을 구할 범위를, 

두번째 인수로 평균에서 제외할 퍼센트를 준다. 

즉 상위 10%, 하위 10%를 제외하고 평균을 구하려면 20% 또는 0.2를 인수로 사용한다. 

이 방법으로 제안서 평가에 사용해보자. 

퍼센트를 인수로 줘야 한다고해서 반드시 계산해야할 필요는 없고, 전체 숫자 중에서 2개에 해당하는 값을 제외하기 때문에 2/5로 계산해도 상관없다. 

세번째 방법 : SUM - MAX - MIN / 갯수

어쩌면 가장 전통적(?)인 방법. 

전체 값을 구하고, 

범위에서 MAX값과 MIN값을 제외하고 나머지로 평균을 계산하는 방법이다. 

TRIMMEAN함수가 엑셀2007부터 지원하기 때문에 그 이전에는 이런 방법으로 계산했다. 

마지막으로 실무적 조언을 덧붙이면 나누는 수(입에 맞는 말은 '젯수' ^^)를 위 예에서는 편의적으로 "3"으로 적었지만 "COUNT(평균범위)-2"로 계산하는게 맞다. 

제안서 평가를 진행하면서 평가 당일 심사위원이 한 명이 갑자기 불참하는 바람에 미리 준비했던 엑셀 수식을 바꾸느라고 진땀을 흘렸던 기억이...