[함수] 가중평균을 계산하는 방법 (무효 데이터 제외하기 포함)

아래와 같이 학생들의 점수가 분포되어있다고 한다면, 전체 평균은 얼마일까요?

 

90점이 20명, 95점이 50명, 100점이 20명이면 예상하는 평균은

원래 예상하는 평균 = 95점이 되어야 합니다.

 

저 95점을 함수로 구하려면? 가중평균 (SUMPRODUCT/SUM)

1. 원래 가중평균을 구하려면, SUMPRODUCT로 구한 점수의 결과값을, 인원수의 합계인 SUM으로 나누면 됩니다.

 - 평균은 합계/개수 (총점/인원수)라는 사실만 기억하시면 쉽습니다.

=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)

[수식 설명]
SUMPRODUCT (A범위, B범위) = A범위와 B범위의 값을 각각 곱해라.
*아래의 예제를 참고.

위의 예제를 들면, SUMPRODUCT는 이렇게 계산된다.

 

============================

총합은 8,550이고 인원은 총 90명이니까,가중평균은 8550/90 = 95점이 되겠네요.

 

 

(...계속...) 무효가 가운데에 들어가있을때 평균 내기

다만 이번 예제를 위의 수식대로 계산해보면...

SUMPRODUCT = 8550점 (옳음)

SUM = 100명 (틀림)

이 나옵니다.(!)

 

따라서 그냥 SUMPRODUCT/SUM을 쓰면 95점이 아니라, 

계산되면 안되는 인원이 추가되어 보이는 평균 = 85.5이 되어버리죠.

 

이때는 범위지정만으로는 평균을 내기가 까다롭습니다.

우리는 90명 중에 무효인 10명을 제외한 평균값을 내야합니다.

이럴때는 SUMPRODUCT와 ISNUMBER를 활용하여 구할 수 있습니다.

즉, SUM 부분을 아래와 같이 고쳐주면 됩니다.

=SUMPRODUCT(I4:I7,J4:J7)/SUMPRODUCT(--ISNUMBER(I4:I7),J4:J7)

ISNUMBER = 는 셀 값이 숫자인지를 물어보는 함수.
위의 경우, 결과에 따라 참, 참, 거짓, 참의 결과값을 돌려준다.

--(참, 참, 거짓, 참) = 1, 1, 0, 1
새로운 기호가 아니라, 음수 (-1)을 2번 곱한것.
텍스트가 숫자 계산에 사용될 때, 자동으로 숫자로 변경되게 하기 위함.
 수식창에 "= --TRUE" 라고 입력하면 1이 나오는 것과 같은 이치.

 

이번 내용은 난이도가 높습니다. 배열에 대한 이해가 필요하기 때문이죠.

잘 모르겠다면 수식 - 수식계산 기능을 써서, 수식이 어떻게 돌아가는지 점검해보시면 좋을 것 같아요.

 

무효인 데이터를 제외하고 (가중)평균을 계산하는 방법.xlsx

0.01MB

참고: ExtendOffice, How To Ignore Blanks When Calculate The Weighted Average In Excel?