日本語

#NUM! エラー：Excel と Google Sheets の原因と直し方

#NUM! は、関数が有効な数値を返せない、または結果が範囲外のときに出ます。日付を逆にした DATEDIF、SQRT(-1)、LN(-1)、LOG(0)、LOG10(0)、POWER(10,400)、RANDBETWEEN(10,1)、basis 9 の YEARFRAC、FACT(-1) はすべて #NUM! でした。

Excel エラー修正 著者 Oscar 読了目安 3分 公開日
目次

要点

#NUM! は、関数が有効な数値を返せない、または結果が範囲外のときに出ます。日付を逆にした DATEDIF、SQRT(-1)、LN(-1)、LOG(0)、LOG10(0)、POWER(10,400)、RANDBETWEEN(10,1)、basis 9 の YEARFRAC、FACT(-1) はすべて #NUM! でした。

=DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D")                   → #NUM!
=YEARFRAC(DATE(2026,11,1),DATE(2026,11,12),9)                     → #NUM!

確認のポイント

POWER(10,100) は 1E+100、対照の SQRT(9) は 3、FACT(3) は 6 です。LOG(0) は #DIV/0! ではなく #NUM!。DATEDIF を逆にしても負数にはならず #NUM! で、正向は日付チートシートで 11 でした。

注意点と互換性

FACT(1.5) はこのファイルではエラーにならず 1 を返し、FACT(1) と同じです。1.5 の階乗としては使えません。FACT(1.5) は今回デスクトップ Excel では再テストしていません。INDEX はシート範囲のはみ出しが #NUM!、配列定数のはみ出しが #REF! です。

英語の詳細例・数式・著者メモ

Which versions this applies to

Every result below comes from Google Sheets on November 12, 2026. Microsoft documents #NUM! in Excel when a formula produces a number that is too large or is otherwise invalid for the function. I did not re-test this set in desktop Excel today.

=DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D")                   → #NUM!
=YEARFRAC(DATE(2026,11,1),DATE(2026,11,12),9)                     → #NUM!

DATEDIF wants the start date first. Swapping 12 November and 1 November did not return a negative 11; it returned #NUM!. The forward direction, in the date and time cheat sheet, returned 11. If you hide this with IFERROR, you will also hide a reversed range.

YEARFRAC accepted the same two dates with the default basis and with basis 1. Basis 9 is not a legal basis value, and that call returned #NUM!.

Cause 2: A math function on a number outside its domain

=SQRT(-1)     → #NUM!
=LN(-1)       → #NUM!
=LOG(0)       → #NUM!
=LOG10(0)     → #NUM!
=SQRT(9)      → 3

Square root and the logarithms are not defined for those inputs on the real line this spreadsheet uses. SQRT(9) returned 3, so the functions themselves were working. Zero in LOG or LOG10 was #NUM!, not #DIV/0!. For a genuine divide-by-zero, see #DIV/0! Error.

Cause 3: A result that is too large, or a range that is backwards

=POWER(10,100)     → 1E+100
=POWER(10,400)     → #NUM!
=RANDBETWEEN(10,1) → #NUM!

10^100 was fine. 10^400 was not; the result is outside the number range the sheet will store, and the error is #NUM!. RANDBETWEEN wants the lower bound first. RANDBETWEEN(10,1) returned #NUM!. Swap the arguments, or use RANDBETWEEN(MIN(a,b),MAX(a,b)) if the two inputs might arrive in either order. I did not test that wrapper; I tested the bare backwards call.

Cause 4: FACT of a negative number, and what 1.5 did

=FACT(-1)    → #NUM!
=FACT(3)     → 6
=FACT(1.5)   → 1

A negative factorial is #NUM!, and FACT(3) is 6. FACT(1.5) did not error in this Google Sheets file; it returned 1, the same value as FACT(1). I would not treat that as the mathematical value of 1.5!. I did not re-run FACT(1.5) in desktop Excel today.

#NUM! is not #REF! or #VALUE!

INDEX(A2:A4,20) in the same file returned #NUM!, while INDEX({10,20,30},20) returned #REF!. That pair is unpacked in #REF! Error in Excel and Google Sheets. A wrong type of argument, such as adding text that will not coerce, is #VALUE!.

Excel and Google Sheets differences

Microsoft describes #NUM! for invalid numeric arguments and for numbers outside Excel’s range. The DATEDIF start-after-end case is also documented as #NUM! for Excel. I did not re-run this set, including FACT(1.5), in desktop Excel today.

Common mistakes

  • Wrapping a reversed DATEDIF in IFERROR and reading the blank as “zero days.” The error was #NUM!, not 0.
  • Expecting LOG(0) to be #DIV/0!. It was #NUM!.
  • Calling RANDBETWEEN with the larger number first. That was #NUM!.
  • Assuming FACT(1.5) is an error, or that it equals 1.5 factorial. In this file it returned 1, with no error.

FAQ

Why is DATEDIF showing #NUM! for two real dates?

The start date is after the end date. DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D") was #NUM!. Put the earlier date first, or use DAYS, which in the cheat-sheet test returned 11 in the forward direction.

Why did SQRT(-1) not return a complex number?

This spreadsheet’s SQRT returned #NUM! for -1. It is working with real numbers. SQRT(9) was 3.

How large a POWER result can Sheets store?

POWER(10,100) returned 1E+100. POWER(10,400) returned #NUM!. I did not hunt for the exact cutoff between those two exponents.

Does FACT always error on a decimal?

Not in this Google Sheets file. FACT(1.5) returned 1. FACT(-1) was #NUM!. I did not re-test FACT(1.5) in Excel today.

Is a backwards RANDBETWEEN a #VALUE! typo?

No. RANDBETWEEN(10,1) was #NUM!. The function name was recognized; the bounds were not usable in that order.

Live-test findings

Google Sheets, November 12, 2026. #NUM! from DATEDIF with 12 Nov then 1 Nov; SQRT(-1); LN(-1); LOG(0); LOG10(0); POWER(10,400); RANDBETWEEN(10,1); YEARFRAC with basis 9; and FACT(-1). POWER(10,100) returned 1E+100. FACT(3) returned 6. FACT(1.5) returned 1. SQRT(9) returned 3.

Sources

Oscar

表計算の数式とトラブル解決を紹介。検証環境と未確認事項は各記事を参照。

Tested in Google Sheets, November 12, 2026. Excel version notes follow Microsoft documentation and were not re-tested in a desktop Excel workbook on this date..

#num-error#datedif#errors#formulas
次の記事：シートのエラーと動的配列 IMPORTRANGE の #REF!「You need to connect these sheets」を修正
Excel エラー修正

#DIV/0! エラー：Excel と Google Sheets の原因と直し方

#DIV/0! はゼロ除算です。この Google Sheets では、1/0、10/0、空白での 10 の除算、空文字列での除算、0/0、文字列の 0 での 1 の除算、MOD(5,0)、QUOTIENT(5,0) がすべて #DIV/0! になりました。対照の 10/2 は 5 です。

読了目安 5分
Excel エラー修正

#VALUE! エラー：Excel と Google Sheets の原因と修正

#VALUE! は引数の型や値が不正なときに出るエラーで、検索キーがないことではありません。"SKU-101"+10、FIND や SEARCH で見つからない場合、VLOOKUP の列番号 0 はいずれも #VALUE! でした。キーなしは #N/A、列番号が大きすぎると #REF! です。

読了目安 7分
Excel エラー修正

#REF! エラー：Excel と Google Sheets の原因と直し方

#REF! は無効な参照、または既に値があるセルへ書き込もうとしたときに出ます。4列範囲で 5 列目を指定した VLOOKUP、配列定数をはみ出す INDEX、存在しないシートの INDIRECT、シート外の OFFSET、既存セルへこぼれる SPLIT は、いずれも #REF! でした。

読了目安 5分