COMPUTER EDUCATION 5

Week 3: IF Function
The IF function in Excel is a logical function used to perform conditional
tests. It evaluates a given condition and returns one value if the condition is
TRUE and another value if the condition is FALSE. This function is commonly
used for decision-making processes in excel.
IF Function Formula/Syntax
The IF function is a logical function used in Excel and other spreadsheet
programs to perform conditional tests. It evaluates a specific condition, and
based on whether that condition is true or false, it returns one of two values.
The formula follows this structure:
=IF(logical_test, value_if_true, value_if_false)
Where:
 logical_test: This is the condition you want to evaluate. It can be any
expression or comparison that results in either a true or false outcome.
For example, you could check if a cell contains a number greater than
10 (e.g., A1>10) or if two cells are equal (e.g., B1=C1).
 value_if_true: This is the value that will be returned if the logical test
evaluates to true. This can be a number, text, a formula, or a reference
to another cell. For example, you could return a specific message like
"Pass" or a calculated value if the condition is true.
 value_if_false: This is the value that will be returned if the logical test
evaluates to false. Similar to the value_if_true, this can also be a
number, text, or formula. For example, you could return "Fail" or leave
it blank (e.g., "") if the condition is false.

Here's an example to put it all together:

=IF(A1>10, "Greater than 10", "Not greater than 10")
In this case:
 If the value in cell A1 is greater than 10, it will return the text "Greater
than 10".
 If the value in A1 is not greater than 10, it will return the text "Not
greater than 10".

Real-Life Example of IF Function:

1. Student Grading System:
Unit I Page 1
 o Suppose you have a list of student scores, and you want to
determine whether a student has passed or failed. You can use
the following formula:
o =IF(A2>=75, "Pass", "Fail")
o If the score in A2 is 75 or above, it will return "Pass"; otherwise,
it will return "Fail."
2. Sales Performance Bonus Calculation:
o If a salesperson achieves sales above $5000, they get a bonus;
otherwise, they don’t.
o =IF(B2>5000, "Bonus Awarded", "No Bonus")
3. Inventory Stock Alert:
o If the stock of an item falls below 10, a warning should appear.
o =IF(C2<10, "Low Stock", "Sufficient Stock")
Comparison Operators Used in IF Function:
 Less Than (<) - Checks if a value is smaller than another.
o Example: =IF(5<10, "Yes", "No") → Returns "Yes" because 5 is
less than 10.
 Greater Than (>) - Checks if a value is larger than another.
o Example: =IF(15>10, "Yes", "No") → Returns "Yes" because 15 is
greater than 10.
 Less Than or Equal To (<=)
o Example: =IF(A1<=100, "Within Budget", "Over Budget")
 Greater Than or Equal To (>=)
o Example: =IF(A1>=18, "Adult", "Minor")
 Equal To (=)
o Example: =IF(A1=100, "Perfect Score", "Keep Trying")
 Not Equal To (<>)
o Example: =IF(A1<>50, "Not 50", "Exactly 50")

Week-4: Introduction to Data Validation

Unit I Page 2
Data Validation in Excel is a tool used to control the type of data that can
be entered into a cell or range of cells. It helps improve data accuracy and
prevent incorrect information from being entered.
Common Uses of Data Validation:
1. Allow Whole Numbers Only
To restrict users to enter only whole numbers (integers) in a specific cell or
range, you can apply data validation that ensures only whole numbers can
be input. Whole numbers are numbers without fractions or decimals, and this
type of validation is useful for scenarios where you expect to handle
quantities, counts, or other data that do not require fractional values. The
validation can be set to reject any input that contains decimals or non-
numeric characters. For instance, you could set a rule to only allow values
greater than or equal to zero, ensuring the entry is a positive integer or zero.
Example: In Excel, you can apply data validation by going to the "Data" tab,
clicking "Data Validation," and choosing the "Whole number" option in the
settings. You can further specify a range of acceptable values, such as from
1 to 100, depending on your needs.
2. Allow Decimal Numbers
When you want to allow decimal numbers, you would set the validation to
accept numeric entries with decimal points. This is essential for cases like
entering measurements, prices, or any data requiring precision beyond
whole numbers. Data validation can be used to accept numbers with
decimals, ensuring that the input remains numeric and can support fractions,
like 3.14, 12.99, or any other floating-point numbers.
In Excel, this can be done by setting the data validation type to "Decimal" in
the Data Validation settings. You can define minimum and maximum values
for the decimal numbers as well, ensuring the input falls within a specified
range.
3. Restrict to a List of Items
To restrict users to select from a predefined list of options, you can create a
drop-down list. This feature is useful when you want to ensure consistency
and limit input to a set of valid choices, such as gender options ("Male",
"Female"), departments within a company, or categories in a survey. This list
can be created manually or by referencing a range of cells that contain the
list values.
In Excel, you can set up this validation by selecting the "List" option under
Data Validation settings. You can either type the items directly into the
"Source" field (separated by commas) or reference a range of cells that
contains the options.

Unit I Page 3
4. Restrict to Date Range
If you want to ensure that users only enter dates within a specific range,
such as restricting entries to a project timeline or a fiscal year, you can apply
a data validation rule that enforces the date range. This will prevent users
from entering dates that fall outside of the specified period, ensuring that all
date-related data remains consistent and within the boundaries of the
defined timeframe.
For example, you might restrict a date entry to be within the range of
January 1, 2023, to December 31, 2023. In Excel, you can choose the "Date"
option under Data Validation and then set the start and end dates for the
range.
5. Restrict to Time Range
Similarly to restricting date ranges, you can restrict time entries to a
specified time period. This validation ensures that users can only input times
that fall within a particular window, such as limiting work hours to 9 AM to 5
PM. This is useful in cases like time tracking, scheduling, or other scenarios
where you need to enforce specific time constraints.
In Excel, you would choose the "Time" option under Data Validation and set
the minimum and maximum time range, ensuring the entered time is valid
and falls within the specified hours.
6. Restrict to Text Length
To ensure that text entries meet specific length requirements, you can set a
data validation rule that limits the number of characters users can input. This
is helpful in situations such as enforcing a character limit for usernames,
email addresses, product descriptions, or any other text-based fields where a
certain length is required or expected. You can define both minimum and
maximum lengths for the text, such as requiring a username to be between
5 and 10 characters long.
In Excel, you can set this validation by choosing "Text Length" under Data
Validation, where you can specify the minimum and maximum character
count for the text entry. This helps maintain uniformity and prevents
excessive or incomplete inputs.

Real-Life Scenario for Data Validation:

 Employee Attendance Tracking: Restrict attendance logs to only
valid dates.
 Product Order Form: Prevent users from entering incorrect product
IDs.

Unit I Page 4
  Customer Feedback Form: Limit character count in the feedback
section.

Week 5: Introduction to AND and OR Operators

In Excel, AND and OR are logical operators used to evaluate multiple
conditions at the same time. These functions return either TRUE or FALSE
based on whether the conditions specified are met.
AND Function:
 The AND function returns TRUE if ALL the conditions are TRUE.
 If even one condition is FALSE, it will return FALSE.
 Formula: =AND(condition1, condition2, condition3, ...)
Example:
 Checking Employee Eligibility for Bonus
o If an employee worked more than 40 hours AND completed
all tasks, they receive a bonus.
o =AND(A1>40, B1="Completed")
o Returns TRUE if both conditions are met, otherwise FALSE.
OR Function:
 The OR function returns TRUE if AT LEAST ONE of the conditions is
TRUE.
 It only returns FALSE if ALL conditions are FALSE.
 Formula: =OR(condition1, condition2, condition3, ...)
Example:
 Checking Student Pass Condition
o If a student scores at least 50 in Math OR 50 in Science, they
pass.
o =OR(A1>=50, B1>=50)
o Returns TRUE if either subject has 50 or more.
Real-Life Uses of AND and OR Functions:
1. Hiring Decision:
o An applicant must have 5+ years of experience AND a
relevant degree.

Unit I Page 5
 o =AND(A1>=5, B1="Degree Holder")
2. Discount Eligibility:
o Customers get a discount if they are either loyalty members
OR purchase more than 10 items.
o =OR(A1="Loyalty Member", B1>10)
3. Loan Approval:
o A person qualifies for a loan if their credit score is above 700
AND their monthly income is more than $3000.
o =AND(A1>700, B1>3000)

Summary:
 IF Function is used for decision-making.
 Comparison Operators (<, >, =, <>) help compare values.
 Data Validation ensures data accuracy.
 AND Function checks if all conditions are met.
 OR Function checks if at least one condition is met.

References:
 Google Play. (n.d.). Microsoft Excel: Edit spreadsheets on the go.
Google Play Store. https://play.google.com/store/apps/details?
id=com.microsoft.office.excel&hl=en
 ExcelChamps. (n.d.). Excel basics: A beginner's guide to understanding
Excel. ExcelChamps. https://excelchamps.com/excel-basics/

Unit I Page 6