Overwriting error message #NUM! with meaningful text
 

In my Shortwave Skip Distance Predictor I have the formula
=2*D2*SQRT((B2/C2)*(B2/C2)-1) When C2 is greater than B2 (i.e. when the
critical frequency is greater than the user frequency) we end up taking the
square root of a negative number, which gives #NUM! in the Skip Distance
result column. How do I change this error message into a meaningful text
saying NVIS (Near Vertical Incident Skywave)?

From looking at various Excel Guides it seems I need to add some sort of
Macro, but don't know what General Script to write. If someone could write
one for me I would be most grateful. Given this example I think I could cope
with similar cases in the future. Many thanks.

Add an ISERROR() check first:

=IF(ISERROR(2*D2*SQRT((B2/C2)*(B2/C2)-1)),"NVIS",2*D2*SQRT((B2/C2)*(B2/C2)-1))
--
Regards,
Dave


"Radio Ham" wrote:

In my Shortwave Skip Distance Predictor I have the formula
=2*D2*SQRT((B2/C2)*(B2/C2)-1) When C2 is greater than B2 (i.e. when the
critical frequency is greater than the user frequency) we end up taking the
square root of a negative number, which gives #NUM! in the Skip Distance
result column. How do I change this error message into a meaningful text
saying NVIS (Near Vertical Incident Skywave)?

From looking at various Excel Guides it seems I need to add some sort of
Macro, but don't know what General Script to write. If someone could write
one for me I would be most grateful. Given this example I think I could cope
with similar cases in the future. Many thanks.

It works!!
Regards

"David Billigmeier" wrote:

Add an ISERROR() check first:

=IF(ISERROR(2*D2*SQRT((B2/C2)*(B2/C2)-1)),"NVIS",2*D2*SQRT((B2/C2)*(B2/C2)-1))
--
Regards,
Dave


"Radio Ham" wrote:

In my Shortwave Skip Distance Predictor I have the formula
=2*D2*SQRT((B2/C2)*(B2/C2)-1) When C2 is greater than B2 (i.e. when the
critical frequency is greater than the user frequency) we end up taking the
square root of a negative number, which gives #NUM! in the Skip Distance
result column. How do I change this error message into a meaningful text
saying NVIS (Near Vertical Incident Skywave)?

From looking at various Excel Guides it seems I need to add some sort of
Macro, but don't know what General Script to write. If someone could write
one for me I would be most grateful. Given this example I think I could cope
with similar cases in the future. Many thanks.

Note that the formula will mask other errors, too (like an error B2, C2
or D2, or C2=0). It also uses rather more references than necessary. A
slightly more efficient version:

=IF(B2<C2,"NVIS",2*D2*SQRT((B2/C2)^2-1))

In article ,
"David Billigmeier"
wrote:

Add an ISERROR() check first:

=IF(ISERROR(2*D2*SQRT((B2/C2)*(B2/C2)-1)),"NVIS",2*D2*SQRT((B2/C2)*(B2/C2)-1))