Excel IFNA Function
Excel IFNA Function
The IFNA in Excel is used to remove the #N/A type of errors in excel values cells with our choice of value. IFNA function is from Excel 2013 onwards, so it is not available in earlier versions of Excel.
Syntax
IFNA(Value,[Value_if_error])
Parameter
Value(required)- This parameter represents the first cell value, which is checked if there is an error if the cell.
Value_if_error( optional)- This parameter represents the error value, which will be replaced by this value If in the cell there is a value of #N/A.
Return
The function returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument.
Example 1
Objective: Using IFNA function, represent the Col A with #N/A value as 9999.
Col A | With IFERROR &Formula | |
1 | 1 | =IFNA(A19,9999) |
#N/A | 9999 | =IFNA(A20,9999) |
Example 2
Objective: Find the monthly salary of each employee using data in Annual Salary, and if #N/A appears, then change it to ERROR IN SALARY.
Emp Name | Designation | Annual Salary | Monthly Salary | Formula Used |
Ashish | AVP | #N/A | - | =IFNA(A20,"-") |
Sohrab | Analyst | £50,000 | 4167 | =IFNA(A21,"-") |
Paul | Engineer | #N/A | - | =IFNA(A22,"-") |
Farouk | Analyst | £40,000 | 3333 | =IFNA(A23,"-") |