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

Related Topics

Excel SMALL() Function

The SMALL() function in excel returns the k-th smallest value in a data set. Syntax SMALL(Array, kth small number from the list) Parameter Array (required)- This parameter represents the list of numbers from which nth smallest...

1 minute read.

Excel AND() Function

The AND() function in excel tests the multiple sets of conditions and returns TRUE if all supplied conditions met the criterion, FALSE otherwise. You need to have a minimum of 2 conditions...

2 minutes read.

Excel EDATE() Function

Excel EDATE() Function The EDATE() function in excel returns the serial number of the date that is the indicated number of months before or after the start date. Syntax EDATE(Start_Date, Month) Parameter Start_Date(required)- This parameter represents...

1 minute read.

Decimals in Excel

Microsoft Excel is widely used for calculation purposes for various applications. The calculation consists of numbers which include whole numbers, decimals, integers etc. To perform quick calculations, the numbers should...

5 minutes read.

Calculating the Last Day of the Month in Excel

In Microsoft Excel, various functions exist to calculate the date and time from the current date to the past and future. Among multiple tasks in this tutorial, let's see how...

3 minutes read.

How to remove duplicate values from excel?

Remove duplicate values from excel Duplicate values occur when the same date or set of values is repeated in your Excel sheet. While working with Excel sheets many a time, the...

5 minutes read.

Excel Ribbon Toolbar

Excel Ribbon/Toolbar How does a toolbar look like? The top section of an excel file is called a Ribbon or a toolbar. It has various commands which can be useful for us in our daily work....

6 minutes read.

Array Formula in Excel

How to use Array Formula in Excel? By default, Excel consists of various formulas for calculating the data. Among various formulas, the Array formula is a powerful one that is used...

8 minutes read.

Excel SUBSTITUTE() Function

The SUBSTITUTE() function in excel substitutes/replaces the old text with the new text in a string. SUBSTITUTE function is used to clean data. This function is case-sensitive. Syntax SUBSTITUTE(Text, Old_Text, New_Text, [instance_number]) Parameter Text (required)- This...

2 minutes read.

Locate Maximum Values in Excel

Excel is a combination of numeric values and numbers. To retrieve the result, Excel provides various formulas and functions. Sometimes the user needs to find the largest value from the...

5 minutes read.

Data Validation in Excel

What is Data Validation? Data Validation is one of the features in Excel that allows user to restrict values which other people can fill in – for example, in a form, you may...

4 minutes read.

Trendline in Excel

A Trendline is a line imposed on a graph to predict the direction of the data. It connects the series of data together to showcase the data best fit. The...

3 minutes read.

Multiplication in Excel

Multiplication plays a vital role in Mathematics. Usually, the calculations are performed manually and automatically. Manual calculations take time and sometimes leads to error, and it is a challenging process...

5 minutes read.

Shortcut Keys in Excel

Shortcut Keys in Excel Shortcut keys in Excel are used to simplify the work and save time by pressing a few keys. These keys are used for navigating and working with Excel spreadsheets quickly and easily. Basic Selection:...

9 minutes read.

Adding Column in Excel

Excel spreadsheet is a combination of rows and columns. After creating the table, there is a need to insert additional row or columns. To organize a better worksheet for calculations,...

5 minutes read.

Excel MINUTE() Function

Excel MINUTE() Function The MINUTE() function in excel converts a serial number to minute (0 - 59). Syntax MINUTE (SERIAL_NUMBER) Parameter SERIAL_NUMBER (required)- This parameter represents the date or time from which you want to extract...

1 minute read.

Excel INT() Function

Excel INT() Function The INT() function in excel is used to round a number down to the nearest integer. It removes decimal points from any number value & return rounded down number value in...

1 minute read.

Nearest Multiple in Excel

In Excel, the values in Multiplication can be round up, down and to nearest Multiplication number using Excel default function. This makes the calculation to be performed in an easier...

3 minutes read.

Date and Time in Excel-VBA

In this modern world of computer technology the creation of Excel reports are termed to be the crucial elements, and the particular type of programs that helps us in performing...

6 minutes read.

Count Cells with Text in Excel

Excel spreadsheets are widely used in many fields to store and analyze data. Usually, the cells present in excel are a combination of numbers and text. To perform the calculation...

3 minutes read.