Lỗi

NUM! trong Excel xảy ra khi các công thức Excel không thể thực hiện các phép tính. Lỗi

NUM! xuất hiện khi công thức hoặc hàm chứa giá trị số không hợp lệ. Vậy để khám phá chi tiết nguyên nhân và cách sửa lỗi

NUM! trong Excel, hãy cùng UniTrain xem qua các ví dụ minh họa cụ thể trong bài viết dưới đây.

Ví dụ minh họa và cách sửa lỗi

NUM!

Ví dụ 1: Số quá lớn hoặc quá nhỏ

Excel giới hạn số lượng nhỏ nhất và lớn nhất mà chúng ta có thể sử dụng. Nếu cố gắng tính toán với các số nằm ngoài phạm vi này, bạn sẽ nhận được kết quả trả về là thông báo lỗi

NUM!.

Ví dụ, công thức tính giá trị của 1 mũ 500, nằm ngoài phạm vi cho phép sẽ trả về thông báo lỗi

NUM!:

\=1^500 // trả về lỗi

NUM!

Ví dụ 2: Lỗi

NUM! xuất hiện nếu không thể thực hiện phép tính

Lỗi

NUM! cũng có thể xuất hiện trong trường hợp nếu không thể thực hiện phép tính. Ví dụ, ảnh chụp màn hình dưới đây mô tả cách sử dụng hàm SQRT để tính căn bậc 2 của một số. Công thức trong ô C3 được sao chép có dạng:

\=SQRT(B3)

Trong pp C5, công thức trả về lỗi

NUM! vì phép tính không thể thực hiện được. Nếu muốn lấy căn bậc 2 của một giá trị âm (coi là giá trị dương), chúng ta có thể sử dụng hàm ABS như dưới đây:

\=SQRT(ABS(B3))

Ngoài ra bạn cũng có thể sử dụng hàm IFERROR để bẫy lỗi và trả về kết quả trống (“”) hoặc thông báo tùy chỉnh.

Ví dụ 3: Đối số hàm không chính xác

Đôi khi lỗi

NUM! cũng có thể xảy ra trong trường hợp nếu chúng ta không cung cấp đầu vào hợp lệ cho đối số của hàm. Ví dụ hàm DATEDIF tính tổng số ngày từ hai khoảng thời gian cho trước. Hàm này lấy 3 đối số như dưới đây:

\=DATEDIF (ngay bat dau, ngay ket thuc, don vi)

Miễn là đầu vào hợp lệ, hàm DATEDIF sẽ trả về tổng số ngày từ 2 khoảng thời gian cho trước.

Tuy nhiên nếu ngày bắt đầu lớn hơn ngày kết thúc, hàm DATEDIF sẽ trả về lỗi

NUM!. Trong ví dụ dưới đây bạn có thể thấy các công thức hoạt động khá ổn cho đến hàng thứ 5, trong đó ngày bắt đầu lớn hơn ngày kết thúc. Trong ô D5 công thức sẽ trả về lỗi

NUM!.

Lưu ý, lỗi này hơi khác so với lỗi

VALUE!, thường xảy ra khi một giá trị đầu vào không đúng loại (số hay văn bản). Để sửa lỗi

NUM!, chỉ cần đổi ngày bắt đầu và ngày kết thúc cho nhau trong hàng thứ 5.

Ví dụ 4: Công thức lặp không tìm thấy kết quả

Một số hàm Excel như IRR, RATE và XIRR dựa vào phép lặp để tìm kiếm kết quả. Tuy nhiên vì lý do hiệu suất, Excel giới hạn số lần lặp. Nếu không tìm thấy kết quả nào trước khi đạt đến giới hạn này, công thức sẽ trả về lỗi

NUM!.

Are you staring at your computer screen, sipping on your coffee, and wondering why your Excel spreadsheet is suddenly showing a strange error message with the hashtag symbol? It is frustrating, confusing and downright annoying, but fear not! We're here to unravel the mystery of the

NUM error in Excel and help you fix it.

What does

NUM error mean in Excel?

The

NUM error in Excel is a common error message triggered by invalid numeric values, leading to an impossible outcome. This error typically occurs when a function contains incorrect arguments, or when the supplied numbers or dates are not valid numeric values, or when a calculation exceeds the limit of what Excel can handle.

The

NUM error can be resolved in a number of different ways depending on the underlying cause. These can include adjusting input values, changing the formula or calculation, or modifying Excel's calculation settings. A particular solution depends on the specific error and the context of its occurrence.

NUM error in Excel – causes and solutions

Below, we will delve into each possible cause of the

NUM error in more detail and provide you with the necessary steps to resolve it.

Invalid input arguments

Reason: Some Excel functions require specific input arguments. If you provide invalid arguments or incorrect data types, Excel may produce a

NUM error.

Solution: Make sure that all input arguments are valid and of the correct data type. Double-check your formula for syntax errors.

For instance, the DATE function in Excel expects a number between 1 and 9999 for the year argument. If the provided year value is outside this range, the

NUM error will occur.

Similarly, the DATEDIF function requires that the end date be always greater than the start date (or both dates are equal). If the opposite, the formula will produce a

NUM error.

For example, this formula will return the difference of 10 days:

=DATEDIF("1/1/2023", "1/11/2023", "d")

And this one will raise the

NUM error:

=DATEDIF("1/11/2023", "1/1/2023", "d")

Too large or too small numbers

Reason: Microsoft Excel has a limit on the size of numbers that it can calculate. If your formula produces a number that exceeds this limit, a

NUM error occurs.

Solution: If your formula produces a number outside the limit, adjust the input values to ensure that the result falls within the allowable range. Alternatively, you can consider breaking the calculation into smaller parts and using multiple cells to arrive at the final result.

The modern versions of Excel have the following calculation limits:

Smallest negative number -2.2251E-308 Smallest positive number 2.2251E-308 Largest positive number 9.99999999999999E+307 Largest negative number -9.99999999999999E+307 Largest allowed positive number via formula 1.7976931348623158E+308 Largest allowed negative number via formula -1.7976931348623158E+308

If you are not familiar with the , I'll briefly explain what it means on an example of the smallest allowed positive number 2.2251E-308. The "E-308" part of the notation means "times 10 to the power of -308", so the number 2.2251 is multiplied by 10 raised to the power of -308 (2.2251x10^-308). This is a very small number that has 307 zeros in the decimal part before the first non-zero digit (which is 2).

As you see, the allowed input values in Excel are between -2.2251E-308 and 9.99999999999999E+307. In formulas, numbers between -1.7976931348623158E+308 and 1.7976931348623158E+308 are allowed. If the result of your formula or any of its arguments is outside the scope, you'll get the

NUM! error.

Please notice that in the current version of Excel 365, only large numbers produce a

NUM error. Small numbers that are outside the limit show up as 0 (General format) or 0.00E+00 (Scientific format). Looks like a new undocumented behavior:

Impossible operations

Reason: When Excel is unable to perform a calculation because it is deemed impossible, it returns the

NUM! error.

Solution: Identify the function or operation that is causing the error and adjust the inputs or modify the formula accordingly.

A typical example of an impossible calculation is attempting to find the square root of a negative number using the function. Since the square root of a negative number cannot be determined, the formula will result in a

NUM error. For example:

=SQRT(25) – returns 5

=SQRT(-25) – returns

NUM!

To fix the error, you can wrap a negative number in the ABS function to get the absolute value of a number:

=SQRT(ABS(-25))

Or

=SQRT(ABS(A2))

Where A2 is the cell containing a negative number.

Another case is the exponentiation of a negative number to a non-integer power. If you try to raise a negative number to a non-integer power, the result will be a complex number. As Excel does not support complex numbers, the formula will produce a

NUM error. In this case, the error indicates that the calculation is impossible to perform within Excel's constraints.

Iteration formula cannot converge

Reason: An iteration formula that cannot find a result causes the

NUM error in Excel.

Solution: Modify the input values to help the formula converge, or adjust the Excel iteration settings, or use a different formula altogether to avoid the error.

Iteration is a feature in Excel that allows formulas to repeatedly recalculate until a certain condition is met. In other words, a formula tries to achieve the result through trial and error. In Excel, there are a number of such functions including IRR, XIRR and RATE. When an iteration formula cannot find a valid result within the given constraints, it returns the

NUM error.

To fix this error, you may need to change the input values or decrease the tolerance level for convergence to ensure that the formula is able to find a valid result.

For example, if your RATE formula results in a

NUM error, you can assist it in finding a solution by providing an initial guess:

Additionally, you may need to adjust the iteration settings in Excel to help the formula converge. Here are the steps to follow:

1. Click on the File menu and select Options.
2. On the Formulas tab, under the Calculation options section, check the Enable iterative calculation option.
3. In the Maximum Iterations box, enter the number of times you want Excel to perform formula recalculations. A higher number of iterations increases the likelihood of finding a result, but also requires more time to calculate.
4. In the Maximum Change box, specify the amount of change between calculation results. A smaller number provides more accurate results but requires more time to calculate.

NUM error in Excel IRR function

Reason: An IRR formula fails to find a solution due to non-convergence issues or when the cash flows have inconsistent signs.

Solution: Adjust Excel's iteration settings, provide an initial guess, and ensure the cash flows have appropriate signs.

The IRR function in Excel calculates the internal rate of return for a series of cash flows. However, if the cash flows do not result in a valid IRR, the function returns a

NUM! error.

As with any other iteration function, a

NUM error in IRR may occur because of the inability to find an outcome after a certain number of iterations. To resolve the issue, increase the maximum number of iterations or/and provide the initial guess for the return rate (see the previous section for more details).

Another reason why the IRR function may return a

NUM error is due to inconsistent signs in the cash flows. The function assumes that there are both positive and negative cash flows. If there is no change in sign for the cash flows, an IRR formula will result in the

NUM error. To fix this issue, ensure that all outgoing payments, including the initial investment, are entered as negative numbers.

In summary, the

NUM error in Excel is a common issue that can occur for various reasons, including incorrect arguments, unrecognized numbers/dates, impossible operations, and surpassing Excel's calculation limits. Hopefully, this article has helped you understand these reasons and effectively troubleshoot the error.