Table of Contents
What is the validation rule to use?
Validation Rules | Description/Usage |
ignorena | Will ignore NA values in the field/column that have any formula with targeted numeric values that are set to NA |
Why use this validation rule?
With this validation rule, you will be able to:
Set a field/column that is the result of a formula to ignore NA values coming from its variables (targets)
Not using the rule will take NA's into account and will result in a NA whatever the rest of the formula is
If all the variables in the formula are set to NA, the result will be NA
How does the validation rule react?
When using this validation rule, it will affect differently the result of a formula when having some of the variables being N/A.
Keywords | Result |
num with ignorena | The result will not display NA, unless all variables are NA |
num without ignorena | The result will display NA, as soon as one variable is NA |
timespan with ignorena | The result will not display NA, unless all variables are NA
If dates are compared, the result will be 0 day |
timespan without ignorena | The result will display NA, as soon as one variable is NA |
date/time/datetime/juliandate (for a best before) with ignorena | The result will be the same as the date and/or time inputted
OR
The result will be NA if the date and/or time is NA |
date/time/datetime/juliandate (for a best before) without ignorena | The result will display NA, as soon as one variable is NA |
How to use this validation rule?
As a FIELD
Using columns A, B and C in the Excel spreadsheet, you can create a field.
Column A will be used for the TITLE
The title will be displayed to add a description regarding your input
Column B will be used for the KEYWORD
The keyword defines what the field will be used for
Column C will be used for the VALIDATION RULE(S)
Optionally, you can add the validation rules to:
specify a limit
add formulas
automate a targeted field, such as making it display a certain value
change the state of a field
Here is an example:
Resulting in if any of A, B and/or C is NA, the NA values will be ignored.
As a COLUMN
Using three consecutive rows (or as we refer to it, 123) in the Excel spreadsheet, you can create a column.
Row 1 will be used for the TITLE
The title will be displayed to add a description regarding your input
Row 2 will be used for the KEYWORD
The keyword defines what the column will be used for
Row 3 will be used for the VALIDATION RULE(S)
Optionally, you can add the validation rules to:
specify a limit
add formulas
automate a targeted column, such as making it display a certain value
change the state of a column
Here is an example:
Resulting in if any of A, B and/or C is NA, the NA values will be ignored.
Available keywords for this validation rule