Étiquette : function

Returns TRUE if the value is a valid cell reference (including named ranges). Returns FALSE for all other inputs (numbers, text, invalid references).

Syntax

ISREF(value)

Key Features

What Returns TRUE?

✔ Standard references (A1, Sheet2!B5)
✔ Named ranges (« Sales Data »)
✔ Structured references (Table1[Column1])

What Returns FALSE?

✖ Numbers/text (123, « A1 »)
✖ Formulas (SUM(A1:A10))
✖ Invalid references (-B1, XYZ!A1 for non-existent sheets)

Special Cases

⚠ Returns TRUE for references to non-existent sheets/tables (e.g., SheetX!A1), as Excel only validates syntax, not existence.

Practical Applications

1. Safeguarding Named Ranges

Prevent #NAME? errors when a named range might be deleted:

=IF(ISREF(ABC), AVERAGE(ABC), « Error: Named range ‘ABC’ was deleted »)

2. Dynamic Formula Validation

Check if a cell contains a valid reference before using:

=IF(ISREF(INDIRECT(B1)), « Valid reference », « Invalid input »)

3. Conditional Formatting

Highlight cells containing references:

=ISREF(A1)  // Applies formatting to reference-containing cells

Example Breakdown

Scenario

A workbook uses a named range ABC for calculations. To avoid #NAME? errors if ABC is deleted:

Original (Risky):

=AVERAGE(ABC)  // Fails with #NAME? if ABC is deleted

Protected Version:

=IF(ISREF(ABC), AVERAGE(ABC), « removed Named  ABC « )

Behaviour:

• If ABC exists → Returns average
• If ABC is deleted → Shows friendly message

Limitations

• Cannot detect circular references or broken links.
• Returns TRUE for syntactically valid but non-existent references (e.g., Sheet99!A1).

For robust reference checking, combine with other functions like IFERROR() or ISERROR().

Its returns TRUE if the number is odd, and FALSE if it is even.

Syntax

ISODD(number)

Arguments

• number (required) – The value to check (can be a cell reference, formula, or direct input).

Key Behavior

✅ Integers: Evaluates normally (e.g., 3 → TRUE, 4 → FALSE).
 Decimals: Truncates decimal places before checking (e.g., 5.9 → TRUE, -2.3 → FALSE).
❌ Non-numbers: Returns #VALUE! for text/logical values (e.g., « ABC », TRUE).
 Counterpart: ISODD(number) = NOT(ISEVEN(number)).

Practical Examples

1. Conditional Formatting for Odd Rows

Goal: Shade every odd-numbered row.

=ISODD(ROW()) 

 Data Categorization

Label numbers as odd/even:

=IF(ISODD(A3), « Odd », « Even ») 

Notes

⚠️ Add-In Requirement: In Excel, ISODD() requires the Analysis ToolPak. The workaround avoids this.
 Alternate Formula: For compatibility, use modulo:

=MOD(TRUNC(A1), 2) = 1 

Returns TRUE if the value is a numeric value (including dates/times, percentages, and formulas that return numbers). Returns FALSE for all other types (text, logical values, errors, blank cells).

Syntax

ISNUMBER(value)

Key Features

• Part of the IS() function family – evaluates without type conversion
• Returns FALSE for empty cells (though references to empty cells may display as 0)
• Critical for numeric validation in formulas and data processing

Practical Applications

1. Handling Empty Cells vs. Zero Values

=IF(ISNUMBER(N42); L42+N42; L42)

• Safely sums values only when N42 contains a number
• Avoids errors when N42 is blank or contains text

2. Data Validation (Numbers Only)

=ISNUMBER(A1)

Use in Data Validation → Custom to:

• Restrict cells to numeric entries only
• Allow numbers but block text/errors

3. Conditional Formatting

Highlight cells containing numbers:

=ISNUMBER(B2)

Special Notes

1. Blank Cell Behavior:
   • Direct blank cells → FALSE
   • References to blanks → May show as 0 (adjust via Excel Options)
2. Text Numbers:
   • « 123 » → FALSE (text string)
   • 123 → TRUE (actual number)
3. Error Handling:
   • All error types return FALSE

Example Solutions

Problem 1: Display Blanks Instead of Zeros

Original formula showing 0:

=IF(ISTEXT(M42); ; L42*M42)

Improved version:

=IF(ISTEXT(M42); «  »; L42*M42)

Problem 2: Safe Summation

Error-prone formula:

=L83+N83

Robust solution:

=IF(ISNUMBER(N83); L83+N83; L83)

Pro Tip

Combine with other functions for advanced checks:

=IF(AND(ISNUMBER(A1); A1>0); « Valid »; « Invalid »)

Validates positive numbers only.

This function returns TRUE if the value is not text (including numbers, errors, logical values, or empty cells). It returns FALSE only if the value is text.

Syntax

ISNONTEXT(value)

Arguments

• value (required): The expression to check (number, text, formula, logical value, error, reference, or name).

Key Features

1. Part of the IS() function family – evaluates without type conversion (e.g., « 123 » is treated as text, not a number).
2. Returns TRUE for empty cells – unlike some other IS functions.
3. Counterpart to ISTEXT():
   ISNONTEXT(value) = NOT(ISTEXT(value))

Practical Applications

1. Data Validation (Prevent Text Entries)

=ISNONTEXT(B41)

• Use in Data Validation (Custom formula) to block text inputs in a cell while allowing numbers/dates/empty cell
• Location: Data tab → Data Validation → Custom → Enter formula

2. Handling Cell Overflow Issues
   When long text might spill into adjacent cells:

• Combine with conditional formatting to flag potential display issues
• Use to trigger warnings when formulas generate unexpected text outputs

3. Clean Data Processing

=IF(ISNONTEXT(A1), « Numeric Data », « Text Data »)

• Useful for data type categorization in reports
• Helps separate text/non-text entries for different processing

Special Notes:

• Returns TRUE for all error types (#N/A, #VALUE!, etc.)
• Blank cells return TRUE (different from some other IS functions)
• Numeric strings (e.g., « 123 ») are considered TEXT (returns FALSE)

Example Setup:

1. Select target cell
2. Data → Data Validation → Custom
3. Enter: =ISNONTEXT(B41)
4. Set error alert style/message
5. Copy validation to other cells as needed

This provides robust control over text inputs while allowing all other value types.

This function returns TRUE if the value is the #N/A error. Otherwise, it returns FALSE.

Syntax

ISNA(value)

Arguments

• value (required) – The expression to check (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name).

Background

• Part of the IS() function family, which evaluates arguments without conversion (e.g., numeric strings remain text).
• Specifically checks for the #N/A error, unlike ISERROR() (which detects all errors) or ISERR() (which excludes #N/A).
• Useful for error handling in lookup functions (VLOOKUP, HLOOKUP, MATCH, etc.).

Example: Handling #N/A in Lookups

Problem:

When using VLOOKUP(), a missing search term returns #N/A, which may disrupt reports or dashboards.

Solution:

Replace #N/A with a custom message while allowing other errors to display normally:

=IF(ISNA(VLOOKUP(B76, D76:E78, 2, FALSE)), « Not found », VLOOKUP(B76, D76:E78, 2, FALSE)) 

How It Works:

1. VLOOKUP(B76, D76:E78, 2, FALSE) searches for B76 in the range D76:D78.
2. ISNA() checks if the result is #N/A:
   • If TRUE → Returns « Not found ».
   • If FALSE → Returns the lookup result (or other errors like #VALUE!).

Key Notes

• Precision: Only suppresses #N/A, preserving other errors (e.g., #REF!, #VALUE!) for debugging.
• Alternatives:
  • Use IFERROR() to handle all errors (not just #N/A).
  • Combine with IFNA()  for cleaner syntax.

When to Use ISNA()

• Data Validation: Flag missing values without masking other errors.
• Conditional Formatting: Highlight cells with #N/A differently from other errors.

This function returns TRUE if the value is a logical value (TRUE or FALSE). Otherwise, it returns FALSE.

Syntax

ISLOGICAL(value)

Arguments

• value (required) – The expression to check (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name).

Background

• Part of the IS() function family, which evaluates arguments without conversion (e.g., numeric strings remain text).
• Commonly used with IF() to validate logical conditions before calculations.
• Useful for conditional formatting and data validation rules.

Examples

1. Converting Logical Values to Text Messages

Replace TRUE/FALSE with custom messages using nested IF():

=IF(ISLOGICAL(H60), IF(H60, « Correct », « Not correct »), H60) 

• If H60 contains TRUE → Returns « Correct »
• If H60 contains FALSE → Returns « Not correct »
• If H60 is non-logical (e.g., text, number) → Returns the original value.

Key Takeaways

• ISLOGICAL() ensures clean handling of logical values in formulas.
• Combine with IF() for user-friendly outputs or conditional formatting for visual cues.
• Avoids errors by explicitly checking for TRUE/FALSE before processing.

This function returns TRUE if the number is even, and FALSE if it is odd.

Syntax

ISEVEN(number)

Arguments

• number (required) – The expression to be checked.

Background

• Accepts any numeric expression.
• Integers are evaluated directly.
• Decimal numbers are truncated before evaluation (e.g., 2.4 becomes 2, -1.6 becomes -1).
• Returns #VALUE! for non-numeric inputs (e.g., text, logical values).
• Counterpart to ISODD():

ISEVEN(number) = NOT(ISODD(number))

Example

Scenario: Apply alternating row colours in a worksheet using conditional formatting.

1. Preferred Method:
   Use ISEVEN() with the ROW() function:

=ISEVEN(ROW())

1. • Applies formatting to even-numbered rows
   • How it works: Divides the row number by 2 and compares the truncated result to the original division. If equal, the row is even.

This function returns TRUE if the value is any error value (including #N/A). Otherwise, it returns FALSE.

• Unlike ISERR(), this function does recognize #N/A as an error.

Syntax

ISERROR(value)

Arguments

• value (required) – The expression to check (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name).

Background

• Part of the IS() function family, which returns a logical value based on the argument.
• The input is not converted during evaluation (e.g., a numeric string remains text).
• Often paired with IF() to validate calculations before processing.
• Useful for conditional formatting and data validation rules.
• For detailed error identification, see ERROR.TYPE() examples.

Example

Suppose you use VLOOKUP() to search a list (e.g., birthdays, order numbers, or contact details).

• Problem:
  Entering =VLOOKUP(B36; D36:E38; 2; FALSE) in B37 returns #N/A if no match exists—undesirable in printed reports.
• Solution:
  Replace the formula in B38 with:

=IF(ISERROR(VLOOKUP(B36; D36:E38; 2; FALSE)); « not found »; VLOOKUP(B36; D36:E38; 2; FALSE))

• • If VLOOKUP() results in any error (including #N/A), it displays « not found ».
  • Otherwise, it returns the lookup result.

Note: Adjust the delimiter (comma/semicolon) based on your Excel regional settings.

This function returns TRUE if the value is an error value. Otherwise, it returns FALSE.

• Exception: Unlike ISERROR(), this function does not recognize #N/A as an error and returns FALSE for it.

Syntax

ISERR(value)

Arguments

• value (required) – The expression (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name) that you want to check.

Background

This function is one of the nine IS() functions that return a logical value based on the argument. The argument in IS() functions is not converted for evaluation—meaning a numeric string is treated as text, not a number.

IS() functions are often used with IF() to pre-test calculations. Their results can also be used in conditional formatting and data validation rules.

For more on identifying specific errors, see the ERROR.TYPE() function examples.

Example

Suppose you want to calculate the average of a range (B26:B28) while avoiding errors if no numbers are present.

=IF(ISERR(AVERAGE(B26:B28)); « Check the input values. »; AVERAGE(B26:B28)) 

IMAGE ISERR

• If AVERAGE(B26:B28) results in an error (except #N/A), the formula returns « Check the input values. »
• Otherwise, it returns the calculated average.

This function returns TRUE if the value argument refers to an empty cell. Otherwise, it returns FALSE.

Syntax

ISBLANK(value)

Arguments

• value (required) – The expression (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name) that you want to check.

Background

This function is one of the nine IS() functions that return a logical value based on the argument. The argument in IS() functions is not converted for evaluation. This means that a string representing a number is treated as text, not as a numeric value.

IS() functions are often used with the IF() function to pre-test calculation results. The output of an IS() function can also be used in conditional formatting and data validation rules.

The function returns FALSE not only for non-empty cell references but also for arguments that are not valid references (e.g., text, numbers, logical values, or errors).

Example

Using cell references to display values can sometimes produce unexpected results. In Figure below, column H contains references to cells in column G. Rows 50–52 use =G50, =G51, and =G52, and column I calculates the average of the three numbers in column H (0, 1, 2). However, this is incorrect because column G does not contain the number 0—it has a blank cell.

The correct solution is shown in rows 54–56. Here, the values in column H are pre-tested for blank cells, avoiding the assumption that a blank entry equals 0. The formula used is:

=IF(ISBLANK(G54); «  »; G54)