엑셀에서 가중 평균을 계산할 때 빈 셀(공백)은 제외하고 계산하는 방법

통계 계산을 할 때 가중 평균은 일반적인 단순 평균 계산보다 더 많이 사용되는 계산 방법입니다.
평균 값을 계산할 때 가중치에 따라 평균을 계산함으로 좀 더 정확한 평균 값을 구할 수 있습니다.

평균을 계산할 때 한 가지 고려해야 할 사항이 있습니다.
값이 공백인 경우 평균을 계산할 때 해당 항목을 포함할지 제외할지를 먼저 판단해야 합니다.

공백, 즉 0 값을 평균 계산에 포함할 경우 평균이 많이 낮아지는 결과를 얻게 됩니다. 따라서 평균을 계산할 때 공백은 무시하고 계산하는 것이 일반적입니다.

이번 글에서는 엑셀에서 가중 평균을 계산할 때 빈 셀(공백)은 제외하고 계산하는 방법에 대해 알아보겠습니다.

단순 평균과 가중 평균의 차이점 비교

단순 평균(일반 평균)은 값을 모두 더한 다음 값의 개수로 나누어 나온 값입니다. 평균 계산에 사용되는 값은 모두 동일한 가중치를 가진다고 보면 됩니다.

반면에 가중 평균은 각각의 값에 가중치를 곱해서 더한 값에 가중치의 합으로 나누어 나온 값입니다. 같은 값이라도 가중치에 따라 가중 평균이 달라질수 있습니다.

글로 설명하면 이해가 바로 안될 수 있으므로 예를 들어 설명해 보겠습니다. 위의 엑셀 시트를 참고하면서 보면 됩니다.

• 단순 평균 : (90 + 60 + 80 + 70) / 4 = 75 (가중치는 의미가 없습니다)
• 가중 평균 : (90*30 + 60*20 + 80*25 + 70*25) / (30 + 20 + 25 + 25) = 76.5

※ 일반적으로 가중치의 합은 100 또는 1이 되는 경우가 많습니다.

만약 가중치의 합이 1인 경우 가중 평균은 분자값만 계산하면 됩니다. (예: 90*0.3 + 60*0.2 + 80*0.25 + 70*0.25)

엑셀에서 가중 평균을 계산할 때 공백(빈셀)을 무시(제외)하고 계산하는 방법

먼저 아래 엑셀 시트 화면부터 먼저 확인해 보겠습니다.

가중 평균과 공백을 무시하고 계산한 가중 평균의 값 차이가 많이 발생하는 것을 확인할 수 있습니다.

똑같은 가중 평균이지만 이처럼 결과 값의 차이가 많이 발생하는 이유는 가중 평균을 계산하는 항목 중에 공백인 항목이 존재하기 때문입니다. 참고로 공백은 0 값으로 계산됩니다.

엑셀에서 가중 평균을 계산하는 수식은 아래 수식과 같습니다.

수식 : =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) 

• SUMPRODUCT : 배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수입니다.

위의 SUMPRODUCT 수식을 일반 수식으로 풀어서 설명하면 =(90*30 + 0*20 + 80*25 + 70*25) / (30 + 20 + 25 + 25)와 같습니다. 따라서 가중 평균 결과값은 64.5가 나오게 됩니다.

이번에는 공백인 ‘수학’ 점수를 가중 평균 계산에서 제외하고 다시 가중 평균을 계산하는 수식을 적용해 보겠습니다.

수식 : =SUMPRODUCT(B2:B5,$C$2:$C$5)/SUMPRODUCT(–(B2:B5<>””),$C$2:$C$5)
    =SUMPRODUCT(B2:B5,C2:C5)/SUMPRODUCT(–(B2:B5<>””),C2:C5)

가중 평균 계산의 분자에 해당하는 =SUMPRODUCT(B2:B5,C2:C5)는 일반적인 가중 평균의 분자 계산식과 동일합니다.

분모의 가중치 합계를 계산하는 수식에서 점수가 공백인 경우 무시하는 로직을 반영해 주면 됩니다. 그러기 위해서 SUMPRODUCT(–(B2:B5<>””),C2:C5)로 수식을 지정해 주었습니다.

공백을 무시한 가중 평균 결과값은 80.63이 나오게 됩니다.

※ 만약 공백이 아니라 0 값인 경우 가중 평균 계산에서 무시하길 원한다면 SUMPRODUCT(–(B2:B5<>0),C2:C5)라고 수식을 지정하면 됩니다. (문자 “0”이 아니라 그냥 숫자 0 입니다.)
분자 계산의 0은 무시하면 됩니다. 어차피 0에 가중치를 곱해도 값은 0이기 때문입니다.

이 글이 도움이 되었기를 바랍니다. ^-^