Table of Contents
The IFNA function in Google Sheets allows users to identify and replace #N/A errors in their spreadsheet data. This function checks if a cell contains the #N/A error and if so, replaces it with a specified value. This is useful for data analysis and presentation, as it ensures that any erroneous values are appropriately handled. To use the IFNA function, simply enter the formula “=IFNA(value, value_if_na)” into a cell, where “value” is the cell to check and “value_if_na” is the value to replace the #N/A error. This function can be used in various scenarios to improve the accuracy and clarity of data in Google Sheets.
IFNA function
The IFNA function evaluates a value. If the value is an #N/A error, return the specified value.
Parts of an IFNA function
IFNA(value, value_if_na)
| Part | Description |
value | Required. The value to check if it is a #N/A error. |
value_if_na | Required. The value to return if the first argument is an #N/A error. |
Sample formulas
IFNA(205, “Na error”)
IFNA(#N/A, “Na error”)
Notes
- If value or value_if_na is an empty cell, IFNA treats the cell’s value as an empty string (“”).
- If value is a range reference, IFNA returns an array formula result with one entry for in-the-range reference.
Examples
| A | B | |
| 1 | Formula | Result |
| 2 | =IFNA(205, "Na error") | 205 |
| A | B | |
| 1 | Formula | Result |
| 2 | =IFNA(#N/A, “Na error”) | Na error |
| A | B | |
| 1 | Formula | Result |
| 2 | =IFNA(A3:A5, “Has na error”) | |
| 3 | #N/A | Has na error |
| 4 | 100/0 | #ERROR |
| 5 | 45 | 45 |
Related functions
- IFERROR: Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
Cite this article
stats writer (2024). How can I use the IFNA function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-ifna-function-in-google-sheets/
stats writer. "How can I use the IFNA function in Google Sheets?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-ifna-function-in-google-sheets/.
stats writer. "How can I use the IFNA function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-ifna-function-in-google-sheets/.
stats writer (2024) 'How can I use the IFNA function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-ifna-function-in-google-sheets/.
[1] stats writer, "How can I use the IFNA function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the IFNA function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.