[태그:] 엑셀수식

엑셀에서 #NUM!이 뜨면 수식에 들어간 숫자가 그 함수의 조건에 맞는지부터 살핀다. SQRT라면 음수인지, DATEDIF라면 날짜 순서가 맞는지, SMALL이라면 몇 번째 값을 찾는지부터 확인한다. IRR처럼 답을 찾아 여러 번 계산하는 함수는 입력 조건이 맞아도 결과를 찾지 못해 같은 오류가 날 수 있다.

숫자는 멀쩡해 보여도 수식에서 어떻게 쓰였는지가 문제일 때가 있다. 숫자 세 개를 놓고 네 번째로 작은 값을 찾는 식이다. 이때 고칠 곳은 숫자의 표시 형식이 아니라 수식의 순위나 참조 범위다.

1. 오류가 나는 계산과 SQRT의 음수

수식이 길다면 오류 셀을 선택하고 수식 입력줄에서 함수 이름과 참조 셀을 본다. Windows 데스크톱 엑셀에서는 수식 탭 → 수식 분석 → 수식 계산으로 들어가 계산 버튼을 누르면 밑줄 친 부분이 어떤 값으로 바뀌는지 따라갈 수 있다.

예를 들어 =SQRT(B2-C2)에서 B2가 8, C2가 13이면 SQRT에 들어가는 값은 -5다. 두 셀에 모두 양수가 적혀 있어도 중간 계산에서 음수가 만들어진다. 수식 계산 창에서 -5가 나오면 B2와 C2의 차이를 확인한다.

SQRT는 음수의 제곱근을 계산하면 #NUM!을 반환한다. =SQRT(-25)는 오류가 나고, =SQRT(25)는 5가 된다.

여기에 ABS를 붙인 =SQRT(ABS(-25))도 5를 반환한다. 다만 이 수식은 -25의 절댓값인 25를 계산한다. 처음부터 부호를 제외한 크기의 제곱근이 필요했다면 맞는 방법이다. 반대로 B2-C2가 양수여야 하는 계산이었다면, 두 셀의 값과 빼는 순서를 고쳐야 한다. ABS를 붙이면 잘못 들어간 부호까지 함께 가려진다.

2. DATEDIF의 시작일과 종료일

=DATEDIF(A2,B2,"D")는 A2를 시작일, B2를 종료일로 읽는다. 시작일이 종료일보다 늦으면 #NUM!이 나온다.

A2가 2026년 10월 7일이고 B2가 2026년 10월 1일이라면 순서부터 확인한다. 열을 거꾸로 참조했다면 수식을 고치고, 원래 종료일에 오타가 났다면 날짜를 수정한다.

단위도 함께 본다. "Y"는 꽉 찬 연수, "M"은 꽉 찬 개월 수, "D"는 일수다. 1년 2개월의 기간에서 전체 개월 수를 원하면 "M"을 쓴다. "YM"은 연도를 제외한 개월 차이를 구할 때 쓴다.

"MD"는 월과 연도를 제외한 일 차이를 구하지만, 음수나 부정확한 결과가 나오는 알려진 제한이 있어 사용이 권장되지 않는다. 단순히 두 날짜 사이의 일수만 필요하다면 실제 날짜 값이 든 셀끼리 =B2-A2로 계산할 수 있다. 이 경우 B2가 더 이르면 음수 일수가 나와 날짜가 역전된 상태도 드러난다.

3. SMALL의 순위와 숫자 개수

=SMALL(A2:A6,4)는 A2:A6에서 네 번째로 작은 숫자를 찾는다. 다섯 칸을 잡았으니 괜찮겠지 싶지만, 그 안에 숫자가 몇 개 들어 있는지는 별개다.

가령 A2부터 차례로 12, 7, 빈칸, ‘미입력’, 20이 들어 있다면 계산할 숫자는 세 개다. 네 번째 숫자가 없으니 #NUM!이 나온다. SMALL은 참조 범위의 빈칸·텍스트·논리값을 제외한다.

옆의 빈 셀에 =COUNT(A2:A6)를 넣으면 숫자 개수를 확인할 수 있다. 화면에는 숫자처럼 보여도 텍스트로 저장된 값은 이 개수에 포함되지 않는다. 그런 셀은 원래 수치 데이터가 맞는지 확인한 뒤 숫자로 변환한다.

순위 k가 0 이하이거나 숫자 개수보다 크면 오류가 나며, 숫자가 하나도 없는 범위도 마찬가지다. 세 번째 값을 찾으려던 것이면 k를 3으로 수정한다. 네 번째 값이 필요한데 데이터를 빠뜨렸다면 참조 범위를 고쳐야 한다.

아래로 복사한 수식에서만 오류가 생겼다면 정상 행과 오류 행의 참조 범위를 나란히 비교한다. 계속 같은 목록을 대상으로 계산하려던 수식이라면 =SMALL($A$2:$A$6,D2)처럼 목록 범위를 고정하고, D2에 들어 있는 순위가 숫자 개수 안에 있는지 확인한다.

4. 거듭제곱과 숫자의 한계

=5^500처럼 계산 결과가 지나치게 커도 #NUM!이 나온다. 이때는 열을 넓히거나 소수점 표시를 줄여도 계산 결과의 크기가 바뀌지 않는다.

엑셀에서 다룰 수 있는 숫자의 최대 크기는 직접 입력하는 값과 수식 결과에서 다르다. 양수 기준으로 직접 입력 가능한 최대값은 9.99999999999999E+307, 수식으로 얻는 최대값은 1.7976931348623158E+308이다. E+307은 10의 307제곱을 곱한다는 표기다. 음수도 절댓값 기준으로 같은 크기의 한계가 있다.

거듭제곱 오류에서는 밑과 지수부터 확인한다. 백분율 5%를 넣어야 하는 곳에 5를 넣었는지, 지수에서 기간을 잘못 참조했는지 살핀다. 계산 목적상 정말 그만큼 큰 결과가 필요하다면 계산 방식이나 도구를 바꿔야 한다. 오류를 없애려고 지수를 줄이면 구하려던 값 자체가 달라진다.

5. IRR·RATE의 부호, 기간, guess

IRR과 RATE는 수익률이나 이자율의 후보를 바꿔 가며 답을 찾는다. 이때 출발점으로 쓰는 추정값이 guess다. 생략하면 두 함수 모두 10%에서 시작한다.

IRR을 쓸 때는 먼저 금액 범위에 양수와 음수가 적어도 하나씩 있는지 본다. 투자한 돈처럼 나간 금액은 음수, 돌려받은 돈은 양수로 넣고 발생 순서대로 배열한다. 초기 투자금이 범위에서 빠져 수입만 남았다면 guess부터 바꿀 일이 아니다.

기간은 매월 또는 매년처럼 일정해야 한다. IRR은 참조 범위의 빈칸과 텍스트를 건너뛰므로, 현금흐름이 없었던 달까지 포함하려면 해당 달에 숫자 0을 넣는다. 빈칸으로 두면 그 기간이 계산 순서에서 빠질 수 있다.

입력과 기간이 맞는데도 결과를 찾지 못하면 =IRR(B2:B7,-10%)처럼 두 번째 인수에 다른 guess를 넣어 볼 수 있다. -10%는 추정값을 지정하는 예다. 예상 결과에 가까운 값을 쓰되, 오류가 사라졌다는 이유만으로 원하는 수익률을 얻었다고 판단하지 않는다.

RATE를 쓸 때는 받은 돈과 지급한 돈의 부호, 총 납입 횟수, 회당 납입액을 맞춘다. 대출금을 양수로 넣었다면 상환액은 음수로 넣는 식이다. 월납인데 총기간에는 연수를 넣는 실수도 살핀다.

예를 들어 4년 동안 매월 200씩 갚고 처음 받은 돈이 8000이라면 다음처럼 입력할 수 있다.

=RATE(4*12,-200,8000,0,0,12%/12)

여기서 4*12는 48개월, 첫 번째 0은 마지막에 남을 잔액, 두 번째 0은 매월 말 납입을 뜻한다. 마지막 12%/12는 연 12%를 월 단위로 바꾼 추정값이다. 월초 납입이면 지급 시점을 뜻하는 인수를 1로 바꾼다. 이 수식의 결과도 월 이자율이다.

RATE는 해가 없거나 여러 개일 수 있다. 부호와 지급 조건을 먼저 맞춘 뒤 guess를 바꿔 보고, 나온 값이 원래 납입 조건에 맞는지 대조한다.

6. XIRR의 금액·날짜 짝

입출금 간격이 일정하지 않다면 날짜를 함께 넣는 XIRR을 사용한다. =XIRR(B2:B7,A2:A7)에서 B열은 금액, A열은 그 금액이 발생한 날짜다.

여기서는 두 범위의 길이를 먼저 맞춘다. 금액은 B2:B7인데 날짜는 A2:A6까지만 잡았다면 #NUM!이 나온다. 같은 행의 날짜와 금액이 실제로 같은 거래에 해당하는지도 함께 본다.

다음으로 첫 번째 날짜를 확인한다. 날짜가 전부 오름차순일 필요는 없지만, 첫 번째 날짜보다 이른 날짜가 뒤에 들어 있으면 #NUM!이 나온다. 정렬해서 고칠 때는 날짜와 금액을 함께 움직여야 한다. 날짜 열만 정렬하면 입출금과 날짜의 짝이 바뀐다.

금액에는 양수와 음수가 적어도 하나씩 있어야 한다. 날짜로 쓸 수 없는 값은 #VALUE!을 낼 수 있으므로, 날짜를 직접 수식에 넣을 때는 DATE(2026,10,7)처럼 만든다.

범위와 날짜·부호를 맞춘 뒤에도 결과를 찾지 못하면 세 번째 인수인 guess를 조정한다. XIRR은 guess를 생략하면 10%에서 시작하고, 날짜 차이를 365일 기준으로 계산해 연 단위 수익률을 구한다.

7. 반복 계산 설정과 오류 표시

IRR·RATE·XIRR의 #NUM!을 보고 곧바로 ‘반복 계산 사용’을 켜는 방법부터 찾을 수 있다. 그런데 함수가 답을 찾기 위해 내부에서 반복하는 계산과, 셀이 자기 자신을 다시 참조하는 순환 참조 계산은 구별해서 봐야 한다.

함수 설명상 IRR은 20번 시도해도 답을 찾지 못하면, RATE는 20번 반복해도 정해진 오차 안으로 결과가 모이지 않으면 #NUM!을 반환한다. XIRR의 설명에는 100번이 제시돼 있다.

최대 반복 횟수는 순환 참조를 계산할 때 적용되는 한도다. 일반 오류 안내에는 반복 횟수를 조정하라는 설명도 있지만, 그 설정으로 세 함수의 내부 한도가 함께 늘어나는지는 확인되지 않았다. 따라서 이 오류가 나면 금액·날짜·기간·guess부터 점검하고 고치는 편이 낫다. 데스크톱의 계산 옵션 변경은 열려 있는 다른 통합 문서에도 영향을 준다.

수정한 결과는 다시 계산된 값으로 확인한다. Windows에서 수동 계산을 쓰고 있었다면 수식 탭의 지금 계산을 누른다. 변경 여부와 관계없이 열려 있는 통합 문서의 모든 수식을 다시 계산하려면 Ctrl+Alt+F9를 쓴다.

마지막으로 =IFERROR(원래수식,"입력 확인")은 오류 대신 문구를 보여 준다. #NUM!뿐 아니라 다른 오류도 처리하므로, 원래 수식과 입력을 확인한 뒤 표시 용도로 붙인다. 특히 오류 대신 0을 표시하면 계산에 실패한 셀에 실제 숫자 0이 들어간다. 합계나 수익률에 연결된 셀이라면 대체값의 의미까지 정해야 한다.