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,"-")



ADVERTISEMENT
ADVERTISEMENT