이 글의 목적은 엑셀에서 발생하는 #NUM! 오류의 의미와 대표 원인을 정확히 진단하고, 함수별·상황별 해결 절차를 단계적으로 제시하여 현장에서 즉시 문제를 재현·수정할 수 있도록 돕는 것이다.
1. #NUM! 오류의 정확한 의미와 진단 절차
#NUM! 오류는 “계산 가능한 숫자 결과 자체가 유효 범위를 벗어났거나”, “수학적으로 해가 존재하지 않거나”, “반복 계산이 수렴하지 못한” 경우에 발생하는 오류이다. 텍스트·형식 문제로 생기는 #VALUE! 또는 참조 문제로 생기는 #REF!와 구분해야 한다.
실무에서 빠르게 원인을 좁히려면 다음 순서로 점검한다.
- 어떤 함수에서 터졌는지 확인한다(수식 입력줄에서 함수명 파악)이다.
- 중간 계산값을 분리하여 검증한다(ALT+= 자동 합계가 아니라, 부분 수식을 선택하고 F9로 계산값만 확인)이다.
- 입력값이 함수의 정의역(domain)에 들어오는지 확인한다(예: SQRT의 입력≥0, LOG의 입력>0, DATEDIF 종료일≥시작일 등)이다.
- 반복 계산이 필요한 금융·해 찾기 함수(IRR, XIRR, RATE, NPER 등)라면 초기 추정값과 부호 변화 조건을 검토한다이다.
- 결과가 너무 크거나 작아 내부 정밀도를 벗어나는지 확인한다이다.
주의 : #NUM!은 “숫자 아닌 값” 문제가 아니라 “숫자 계산 자체가 불능”인 상태에서 주로 발생한다. 텍스트→숫자 변환 실패는 보통 #VALUE!로 나타난다.
2. 대표 원인과 증상 매핑
| 원인 유형 | 전형적 증상 | 핵심 점검·해결 |
|---|---|---|
| 정의역 위반(수학함수) | SQRT 음수, LOG·LN 입력≤0, POWER의 음수 밑과 유리수 지수 조합 등에서 #NUM! 발생 | 입력 범위 사전 필터링, IF·MAX·ABS로 정의역 강제, 예외 분기 설계 |
| 반복 계산 미수렴(금융·해 찾기) | IRR/XIRR, RATE, NPER 등에서 “추정값에 따라 결과 다름”, “일부 케이스만 실패” | 초기 추정값 조정, 현금흐름 부호 변화 조건 검토, 범위 이분법 구현, 최대 반복·허용오차 설정 |
| 비현실적 스케일(오버플로·언더플로) | 거듭제곱·팩토리얼 등에서 값이 너무 커서 내부 한계 초과 | 로그 스케일로 변환, Stirling 근사 등 수치 안정화, 단위 재설정 |
| 날짜·시간 논리 위반 | DATEDIF에서 종료일<시작일일 때 #NUM!, 일부 시간 함수 음수 또는 범위 초과 | 시작·종료 역전 방지, 음수 시간 처리 로직, 기준일·일반수 포맷으로 검증 |
| 순차 계산 중간값 붕괴 | 중간 계산은 정상처럼 보이나 특정 분기에서 급격히 발산하여 최종 단계에서 #NUM! | 중간 단계 캐시(헬퍼 열)로 각 단계의 최대·최소·부호 검사, 경계값 클리핑 |
3. 함수군별 #NUM! 상세 해결 가이드
3.1 기본 수학 함수(SQRT, LOG/LN, POWER, EXP 등)
정의역을 명확히 통제하면 대부분 해결된다.
/* 음수 입력 시 제곱근을 0으로 클리핑 */ =SQRT(MAX(0, A2))
/* log(0) 또는 음수 방지 */
=IF(A2>0, LN(A2), NA())
/* 음수 밑의 유리수 지수 방지: 분모가 짝수면 실수해가 없음 */
=LET(
base, A2, p, B2, q, C2,
IF(AND(base<0, ISEVEN(q)), NA(), base^(p/q))
)주의 : 실무 보고서에서 “정의역 밖 입력을 0 처리”는 결과를 낙관적으로 왜곡할 수 있다. 규칙을 명시하고 예외 건수를 통계로 병기하는 것이 바람직하다.
3.2 금융 함수(IRR, XIRR, RATE, NPER, YIELD, PRICE 등)
내부적으로 해 찾기(뉴턴, 이분 탐색 등) 반복이 수행되며, 다음 조건에서 #NUM!이 발생한다.
- 현금흐름에 부호 변화가 없거나 여러 번 변해 다중 해가 존재할 때이다.
- 초기 추정값이 나쁘거나 해가 수렴 영역 밖에 있을 때이다.
- 기간·일수 계산 규칙 불일치, 날짜 정렬 오류, 중복 날짜 등 데이터 전처리 문제가 있을 때이다.
대응 절차는 다음과 같다.
- 현금흐름 벡터에 음수→양수의 최소 1회 부호 변화가 있는지 확인한다.
- 날짜가 오름차순 정렬되고 중복이 없는지 확인한다(XIRR는 특히 중요)이다.
- 추정값을 다양한 값으로 시도한다(예: IRR(range, 0.1), IRR(range, -0.5), IRR(range, 0.5))이다.
- 필요 시 사용자 정의 이분법 공식을 사용한다.
/* XIRR가 #NUM!일 때 이분 탐색으로 수렴을 유도하는 패턴 */ =LET( cf, C2:C25, dt, B2:B25, f, LAMBDA(r, SUM( cf / (1+r)^((dt-INDEX(dt,1))/365) ) ), lo, -0.9, hi, 1.0, tol, 1E-7, maxit, 200, LAMBDA(lo0,hi0,i, IF(i>maxit, NA(), LET(m, (lo0+hi0)/2, IF(ABS(f(m))<tol, m, IF(f(lo0)*f(m)<0, RECURSE(lo0,m,i+1), RECURSE(m,hi0,i+1)) ) ) ) )(lo,hi,0) )주의 : 현금흐름이 월간·분기별 등 이산 간격이라면 XIRR/XNPV 대신 IRR/NPV를 쓰는 것이 일관성에 유리하다. 날짜와 간격이 뒤섞이면 수렴 실패 가능성이 커진다.
3.3 날짜·시간 함수(DATEDIF, TIME, WORKDAY 등)
- DATEDIF: 종료일<시작일이면 #NUM!이 된다. 입력 순서를 바꾸거나 ABS로 처리한다.
=IF(B2>=A2, DATEDIF(A2,B2,"D"), NA()) /* 또는 무조건 양수 일수 */ =DATEDIF(MIN(A2,B2), MAX(A2,B2), "D")- TIME: 음수 시·분·초, 과도한 범위 입력은 #NUM! 가능성이 있다. 0~23, 0~59, 0~59 범위를 강제한다.
=LET(h, A2, m, B2, s, C2, TIME(MOD(h,24), MOD(m,60), MOD(s,60)))- WORKDAY/NETWORKDAYS: 비영업일·휴일 목록이 잘못되었거나 범위가 비어 있어 계산 범위를 넘어갈 때 경계값을 확인한다.
3.4 분포·특수 함수(팩토리얼, 감마, 베셀 등)
극단적 파라미터에서 오버플로가 발생하면 #NUM!이 나타난다. 로그-도메인으로 변환하거나 근사를 활용한다.
/* 큰 값 곱셈을 로그 합으로 변환 후 지수화 */ =EXP(SUM(LOG(A2:A100)))주의 : 로그-도메인 변환은 0 또는 음수 입력을 허용하지 않는다. 입력 검증을 선행하고 예외 분기를 둔다.
4. 실무 진단 체크리스트
- 오류 셀에 대해 =ERROR.TYPE(셀)을 평가하여 “6”인지 확인한다(#NUM!은 6)이다.
- 수식을 단계별로 분해해 중간 결과를 별도 열에 기록한다이다.
- 입력의 최소·최대·부호·단위를 요약한다이다.
- 반복 계산 관련이면 추정값, 최대 반복 횟수, 허용 오차를 기록한다이다.
- 함수 정의역과 경계값에서의 동작을 표준화한다이다.
=ERROR.TYPE(E2) /* 6이면 #NUM! */ =LET(x, A2, IF(x>0, LN(x), NA())) =MAP(A2:A100, LAMBDA(z, IF(z<0, NA(), SQRT(z))))5. 반복 계산 설정과 수렴 제어(고급)
일부 모델은 순환 참조나 해 찾기 로직을 의도적으로 사용한다. 이때 다음 설정을 적용한다.
- 파일 > 옵션 > 수식 > 반복 계산 사용을 켠다이다.
- 최대 반복 횟수(예: 100~1000)와 최대 변경값(예: 1E-6)을 모델 규모에 맞게 조정한다이다.
- 수렴을 보장하려면 이분 탐색처럼 단조 감소·증가를 이용하는 알고리즘을 수식으로 구현한다이다.
주의 : 반복 계산을 전역으로 켜면 파일 전체에 적용된다. 프로젝트별 표준 값을 문서화하고, 파일 머리글에 설정값을 기재한다.
6. 데이터 경계값 처리 패턴
경계값 처리는 #NUM! 예방의 최전선이다.
/* 하한·상한 클리핑 */ =LET(x, A2, MIN(MAX(x, lower), upper))
/* 0 또는 음수 입력 방지 */
=LET(x, A2, IF(x<=0, NA(), LN(x)))
/* 제어된 예외 처리 /
=IFERROR(formula, NA()) / 차트에서 결측으로 처리되어 단절됨 */7. 현금흐름 함수 불안정 케이스 재현과 복구
7.1 데이터 예시
| 날짜 | 현금흐름 | 비고 |
|---|---|---|
| 2025-01-01 | -100,000 | 투자 |
| 2025-02-01 | 20,000 | 회수 |
| 2025-03-01 | 20,000 | 회수 |
| 2025-04-01 | 20,000 | 회수 |
| 2025-05-01 | 20,000 | 회수 |
| 2025-06-01 | 20,000 | 회수 |
7.2 문제 상황
XIRR이 #NUM!을 반환하는 경우가 있다. 원인은 날짜 정렬·중복, 부호 변화 조건 미충족, 추정값 문제 등이다.
7.3 복구 절차
- 날짜 오름차순 정렬, 중복 제거, 공란 제거를 수행한다이다.
- 부호 변화 횟수 COUNTIF(cf>0)>0, COUNTIF(cf<0)>0인지 확인한다이다.
- XIRR(cf, date, guess)를 여러 guess로 반복해 본다이다.
- 수렴 실패 시 3.2의 이분 탐색 공식을 사용한다이다.
8. 대규모 모델에서의 수치 안정화 전략
- 곱셈 체인은 로그 합으로 바꾸고 마지막에 EXP로 복원한다.
- 극단값은 하한·상한으로 클리핑한다.
- 조건부 분기(IF, IFS)로 정의역을 명시한다.
- 헬퍼 열로 중간값을 고정해 파급 오류를 차단한다.
- 숫자 단위를 축소하거나 정규화하여 값의 크기를 균형화한다.
9. 에러 추적 자동화 템플릿
다음 패턴으로 #NUM!을 자동 태깅하고 원인을 요약할 수 있다.
/* E열: 최종 수식 결과, A~D열: 입력 */ =LET( val, E2, cause, IF(ERROR.TYPE(val)=6, SWITCH(TRUE, OR(A2<0, B2<=0), "정의역", ISBLANK(C2), "데이터누락", ABS(E2)>1E307, "오버플로", "반복미수렴" ), "정상" ), cause )10. 보고서 품질 확보를 위한 운영 규칙
- 정의역 위반을 “결측 처리(NA)”와 “대체값 처리(0·경계값)”로 구분하고 기준을 문서화한다이다.
- 반복 계산을 쓰는 파일은 설정값, 추정값, 수렴 기준을 표로 첨부한다이다.
- #NUM! 건수·비율을 요약 테이블과 차트로 매일 모니터링한다이다.
11. 현장에서 바로 쓰는 레퍼런스 표
| 함수·상황 | #NUM! 발생 조건 | 즉시 해결책 |
|---|---|---|
| SQRT(x) | x<0 | =SQRT(MAX(0,x)) 또는 예외 분기 |
| LN/LOG(x) | x≤0 | =IF(x>0,LN(x),NA()) |
| POWER(a,b) | a<0, b가 유리수이며 분모 짝수 | 짝수 분모 케이스 예외 처리, 정수 지수만 허용 |
| IRR, XIRR | 부호 변화 부재, 데이터 불량, 추정값 불량 | 데이터 정렬, 추정값 스윕, 이분 탐색 |
| RATE, NPER | 해가 존재하지 않거나 수렴 실패 | 초기값 조정, 허용오차·반복 증가, 범위 제한 |
| DATEDIF | 종료일<시작일 | 시작·종료 역전 방지 또는 ABS 처리 |
| TIME(h,m,s) | 음수·범위 초과 파라미터 | MOD로 범위 강제 |
| 대규모 곱·팩토리얼 등 | 오버플로 | 로그-도메인 변환, 근사식 사용 |
12. 거버넌스: 오류 모니터링 지표
- #NUM! 발생 건수, 발생 시트·구역, 함수 유형 분포를 주간 리포트로 집계한다.
- 정의역 위반 비율과 데이터 품질 지표(결측률, 이상치율)를 함께 본다.
- 금융 모델은 수렴 실패율과 평균 반복 횟수를 기록한다.
FAQ
#NUM!과 #VALUE!는 무엇이 다른가?
#NUM!은 숫자 계산 자체가 불능일 때이며, #VALUE!는 보통 타입 불일치나 텍스트 혼입 등이다. 정의역 위반·수렴 실패는 #NUM!로 귀결되는 경우가 많다.
IRR이 어떤 파일에서는 되는데 특정 파일에서만 #NUM!이 나는가?
현금흐름 부호 변화, 날짜 정렬, 중복·공란, 추정값의 차이 때문이다. 데이터를 오름차순으로 정리하고 추정값을 여러 값으로 시도한다.
반복 계산 옵션을 켜면 모든 #NUM!이 해결되는가?
아니다. 반복 계산은 수렴 문제에만 영향을 준다. 정의역 위반이나 오버플로는 별도의 방어 로직이 필요하다.
차트에서 #NUM!이 보이지 않게 하려면?
IFERROR로 NA()를 반환하게 하면 선형 차트에서 해당 점이 그려지지 않는다.
오버플로가 의심될 때 즉시 점검하는 방법은?
중간 곱을 LOG로 전환해 합으로 누적하고, 마지막에 EXP로 복원한다. 또한 값의 크기를 요약해 최대치가 비현실적이면 단위를 조정한다.