Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert #N/A to 0
Hi,
I have a formula that returns #N/A in some cells. I want to be able to convert the #N/A cells to the value 0. I have tried using the ERROR.TYPE formula in an IF function and it converts #N/A cells to 0, however, it also converts the non-#N/A cells to #N/A. I need to keep the value in the non-#N/A cells. According to the help file for this function, I should be able to have the value in the cell returned if it is a false statement. Ex. IF(ERROR.TYPE(E8)=7),0,E8) If E8 = 100, this formula should return 100 and not #N/A for that cell. Any assistance would be greatly appreciated. |
#3
|
|||
|
|||
You would want to modify your formula to make use of the ISNA() formula.
Something like: =IF(ISNA(YourFormulaHere),0,YourFormulaHere) -- HansM "Sheila P" <Sheila wrote in message ... Hi, I have a formula that returns #N/A in some cells. I want to be able to convert the #N/A cells to the value 0. I have tried using the ERROR.TYPE formula in an IF function and it converts #N/A cells to 0, however, it also converts the non-#N/A cells to #N/A. I need to keep the value in the non-#N/A cells. According to the help file for this function, I should be able to have the value in the cell returned if it is a false statement. Ex. IF(ERROR.TYPE(E8)=7),0,E8) If E8 = 100, this formula should return 100 and not #N/A for that cell. Any assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improve Convert function in Excel | Excel Worksheet Functions | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) | |||
Is there a formula to convert numbers to written text in Excel? | Excel Worksheet Functions |