Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheets are overwriting each other | Excel Discussion (Misc queries) | |||
Overwriting of linked data | Excel Discussion (Misc queries) | |||
prevent users from overwriting other users data | Excel Worksheet Functions | |||
Overwriting Cells | Excel Discussion (Misc queries) | |||
How do you create a macros without overwriting the previous one y. | Excel Discussion (Misc queries) |