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

Pivots Table in Excel

What is a Pivot? A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just...

5 minutes read.

Auto Fill and Flash fill

Fill Features Excel provides an amazing feature to fill the data automatically, if the available data is present in the form of any pattern. Instead of entering your data manually, you can use...

3 minutes read.

Cells and Ranges in Excel

Cells and Ranges Each cell is identified by its cell address, which is a combination of the column and row on which that cell is situated. A group of cells together are called ‘Range’. When...

5 minutes read.

Absolute Value in Excel

What is Absolute Value in Excel? Microsoft Excel worksheet is a combination of numeric values and alphabets. The numeric value is either a positive value or a negative value. Sometimes the...

5 minutes read.

How to use TODAY function in Excel

What is TODAY Function in Excel? Today function is one of the types of Worksheet function (WS) in Excel.Today Function belongs to the Date and Time functions.It is used to calculate...

3 minutes read.

Check Marks in Excel

What is a Check Mark? Among various characters checkmark is one of the characters used to indicate that the item or product in the list is correct, chosen or selected. A...

5 minutes read.

Excel IF() Function

Excel IF() Function: The IF() function in excel checks whether a condition is true or not and returns one value if the condition is TRUE and another value if it's...

2 minutes read.

Histogram in Excel

What is Histogram in Excel? “ A histogram is a graphical tool familiar for summarizing discrete or continuous data, where the data are measured on an interval scale.” Karl Pearson described this...

5 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.

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.

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.

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.

Formatting in Excel

Formatting in Excel Formatting in Excel is used to alter and change the appearance of your data in a standardized form. It also helps you to give a professional look to your reports, worksheets,...

7 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.

Excel Function and Formulas

Excel Today() Function The today() function returns the serial number of today’s date as per system number of today’s date as per system’s regional settings. Syntax Today() Parameter NA Category Date & Time Returns This function returns today's date...

4 minutes read.

Conditional Formatting in Excel

Conditional Formatting What are the conditional formats? Conditional Formatting is a tool that allows the user to format cells or range of cells based on the selected condition or given criteria. The formatting will...

4 minutes read.

Division in Excel

Excel worksheet are widely used for calculation purposes. Mathematics calculations include addition, subtraction, multiplication, division etc. Performing this calculation manually takes time and sometimes leads to error. To get the...

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.

Goal Seek in Excel

Goal Seek in Excel The Goal Seek Excel function in Excel facilitates an automatic solving approach by adjusting a few pointers to reach a required output. Goal seeking is used when you have a value in...

3 minutes read.

How to Make Use of the F-Test in Excel

Intro to F-Test in Microsoft Excel F-Test is primarily considered to be the essential tool of statistical in Microsoft Excel that, in turn, is used to do the hypothesis test with...

7 minutes read.