ISNUM ????

R

R-P

I recently learned there is such a thing as ISNA for recognising #N/A (since
using IF(C1="#N/A",... didn't work unless I copied the #N/A from the C1
position...weird...)

Is there such a function for #NUM??? ISNU and ISNUM don't seem to work.
 
G

Gord Dibben

ISERROR or ISERR

See help for the usage of these.

But may be better to find out why you're getting the #NUM! error and work
around it.

From Help...............................

Correct a #NUM! error
Occurs with invalid numeric values in a formula or function.

Click the cell that displays the error, click the button that appears , and
then click Trace Error if it appears.

Review the possible causes and solutions.

Possible causes and solutions
Using an unacceptable argument in a function that requires a numeric argument

Make sure the arguments (argument: The values that a function uses to perform
operations or calculations. The type of argument a function uses is specific
to the function. Common arguments that are used within functions include
numbers, text, cell references, and names.) used in the function are numbers.
For example, even if the value you want to enter is $1,000, enter 1000 in the
formula.

Using a worksheet function that iterates, such as IRR or RATE, and the
function cannot find a result

Use a different starting value for the worksheet function.

Change the number of times Microsoft Excel iterates formulas.

How?

On the Tools menu, click Options, and then click the Calculation tab.

Select the Iteration check box.

To set the maximum number of times Microsoft Excel will recalculate, type the
number of iterations in the Maximum iterations box. The higher the number of
iterations, the more time Excel needs to calculate a worksheet.

To set the maximum amount of change you will accept between calculation
results, type the amount in the Maximum change box. The smaller the number,
the more accurate the result and the more time Excel needs to calculate a
worksheet.

Entering a formula that produces a number that is too large or too small to be
represented in Microsoft Excel


Gord Dibben Excel MVP
 
P

Peo Sjoblom

Agree there, unlike #N/A error #NUM errors most likely point to serious
flaws


--

Regards,

Peo Sjoblom

Gord Dibben said:
ISERROR or ISERR

See help for the usage of these.

But may be better to find out why you're getting the #NUM! error and work
around it.

From Help...............................

Correct a #NUM! error
Occurs with invalid numeric values in a formula or function.

Click the cell that displays the error, click the button that appears , and
then click Trace Error if it appears.

Review the possible causes and solutions.

Possible causes and solutions
Using an unacceptable argument in a function that requires a numeric argument

Make sure the arguments (argument: The values that a function uses to perform
operations or calculations. The type of argument a function uses is specific
to the function. Common arguments that are used within functions include
numbers, text, cell references, and names.) used in the function are numbers.
For example, even if the value you want to enter is $1,000, enter 1000 in the
formula.

Using a worksheet function that iterates, such as IRR or RATE, and the
function cannot find a result

Use a different starting value for the worksheet function.

Change the number of times Microsoft Excel iterates formulas.

How?

On the Tools menu, click Options, and then click the Calculation tab.

Select the Iteration check box.

To set the maximum number of times Microsoft Excel will recalculate, type the
number of iterations in the Maximum iterations box. The higher the number of
iterations, the more time Excel needs to calculate a worksheet.

To set the maximum amount of change you will accept between calculation
results, type the amount in the Maximum change box. The smaller the number,
the more accurate the result and the more time Excel needs to calculate a
worksheet.

Entering a formula that produces a number that is too large or too small to be
represented in Microsoft Excel


Gord Dibben Excel MVP
Click to expand...
 
H

Harlan Grove

David Billigmeier wrote...
=IF(ERROR.TYPE(A1)=6,"This error is shows #NUM","")
Click to expand...

But ERROR.TYPE returns #N/A if its argument doesn't evaluate to an
error, so your formula would return #N/A much of the time. There's a
workaround.

=IF(COUNT(1/(ERROR.TYPE(A1)=6)),"#NUM! error","")
 
H

Harlan Grove

Peo Sjoblom said:
Agree there, unlike #N/A error #NUM errors most likely point to serious
flaws
Click to expand...
....

Maybe, but #NUM! and #DIV/0! are less serious than #REF! and #NAME!. Square
roots of negative numbers, especially between -1E-6 and 0, could be no big
deal.
 
R

R-P

I copied data from another database (Delphi, Borland database, I don't know
and I don't want to know.....I just use it's output).

It was a days-between-two-dates calculation. If either date wasn't filled
in, it would be a void field (resulting in e.g. 38000 days, but I have no
problems filtering this out...) or a field ____-__-__ (where the underscores
are 'spaces'). And this is where the #NUM! kicked in. For the first set of
data, I just overwrote the formula which resulted in #NUM! with "0", but for
future calculations the ISERR and workarounds sound worthwhile.
I want to use the output (which would be one of these three: 30 (days
between 0 and 200), or #NUM! or 38000) to see if the outcome is valid and if
I should use the value in another calculation.

So yes, there are serious flaws, but in my view, the problem lies in the
staff who don't fill the database properly (of which I am one...)

Thanks you all for the help!
 
Top