Nested Formulas and Functions
• A nested formula or function contains more than one
formula/function inside another.
• Each inner formula is placed in brackets.
• The inner formula is calculated first before the outer one is
completed.
• This concept is especially useful in Excel and programming, where
functions like IF, VLOOKUP, or INDEX are often nested to perform
complex tasks.
Conditional IF Function
• Tests a condition and returns one value if TRUE, another if FALSE.
Syntax
• =IF(condition, value_if_true, value_if_false)
Statistical IF Functions
Function Purpose Example
=COUNT(A3:H200)
COUNT Counts all values in a range.
Counts values that meet a =COUNTIF(H3:H200,"Yes")
COUNTIF condition.
=SUMIF(J3:J200,"<0")
SUMIF Adds values that meet a condition.
Calculates average of values that =AVERAGEIF(J3:J200,">1")
AVERAGEIF meet a condition.
Finds the largest value that meets =MAXIFS(A2:A100,B2:B100,"Math",C2:C100,">70")
MAXIFS multiple conditions.
Questions
• What is the purpose of using a named range in a spreadsheet?
• How does a formula differ from a function in spreadsheet software?
• What are the key differences between absolute and relative cell
referencing?
• Give an example of how absolute cell referencing can be applied in a
spreadsheet.
Validation Rules
• Data validation restricts the type of data or values that users can
enter into a cell. It helps:
• Prevent errors
• Guide users with dropdowns or messages
• Ensure consistency in data entry
Common Validation Types
• List Lookup: Restrict input to predefined options (e.g., Male, Female,
Other).
• Range Checks:
• Between two values
• Equal to / Not equal to a value
• Greater than / Less than (or equal to)
• Type Checks: Limit input to integers or real numbers.
• Length Checks: Control the number of characters allowed
Test Data in Spreadsheets
• Normal data refers to valid and acceptable input that should pass the
validation rules without triggering any errors. It represents typical
user entries that meet all criteria.
• Abnormal data is invalid or erroneous input that should fail the
validation and produce an error message. This type is used to test
whether the system correctly blocks incorrect entries.
• Extreme data sits exactly on the boundary of acceptable values. It is
used to confirm that the validation rules correctly accept data that is
just within the allowed limits.
How to create validations
• Go to Data → Data Validation → Data Validation.
• In the dialog box:
• Allow: Choose the type (e.g., Whole Number, Decimal, List, Date).
• Data: Set conditions (e.g., between, equal to).
• Minimum/Maximum: Define limits if applicable