Part 1. Introduce
🌟 Definition 🌟
The ISNUMBER function in Microsoft Excel is a logical function that checks whether or not a given value is numeric. It returns TRUE if the value is a number and FALSE if not.
🌟 Purpose 🌟
The main purpose of the ISNUMBER function is to test the data type of a cell’s content. It is beneficial when you have a large dataset and need to identify or filter cells containing numeric values for further analysis or calculations.
🌟 Syntax & Arguments 🌟
The syntax of the ISNUMBER function is as follows:
=ISNUMBER(value) - Value: This is the value or cell reference you want to test. It can be any data type, such as a number, text, date, or logical value.
🌟 Explain the Arguments in the Function 🌟
- The
valuethe argument is mandatory and represents the value or cell reference you want to check. It can be a single-cell reference, a formula, or a constant value.
🌟 Return Value 🌟
The ISNUMBER function returns either TRUE or FALSE:
- If the value is numeric, it returns TRUE.
- If the value is not numeric, it returns FALSE.
🌟 Remarks 🌟
- The ISNUMBER function recognizes only numeric values, including whole, decimal, and negative numbers. It does not identify text values or other data types as numeric values.
- Excel data are internally stored as numeric values, so the ISNUMBER function will return TRUE for cells containing date values.
🌼 Part 2. Examples 🌼
Let’s explore three examples of using the ISNUMBER function in Microsoft Excel, where we will nest it with other tasks for different scenarios.
🌼 Example 1: Expense Validation 🌼
Suppose we have a table with expenses for different employees, and we want to check if the expense amount is valid (numeric). We’ll use the ISNUMBER function to perform this validation.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Expense 1 | Valid? |
| 2 | John | $100 | =ISNUMBER(VALUE(B2)) |
| 3 | Alice | Not a number | =ISNUMBER(VALUE(B3)) |
| 4 | Bob | $75.50 | =ISNUMBER(VALUE(B4)) |
Explanation:
- We have a table with employee expenses, including Employee names and Expense 1.
- The “Valid?” column uses the ISNUMBER function to check if the Expense 1 value is numeric:
- We use the VALUE function to convert the text representations of numbers to actual numeric values.
- For John (B2), the formula returns TRUE because $100 is a numeric value.
- For Alice (B3), the formula returns FALSE because “Not a number” is not a numeric value.
- For Bob (B4), the formula returns TRUE because $75.50 is a numeric value.
🌼 Example 2: Project Progress Tracker 🌼
Suppose we have a table with the progress of different tasks in a project, and we want to track if the job is completed (100% progress). We’ll use the ISNUMBER function in combination with an IF function to display the completion status.
| A | B | C | |
|---|---|---|---|
| 1 | Task ID | Task Name | Completion (%) |
| 2 | TSK001 | Task A | 80% |
| 3 | TSK002 | Task B | Not started |
| 4 | TSK003 | Task C | 100% |
Explanation:
- We have a table with project tasks, including Task ID, Task Name, and Completion (%).
- The “Completion (%)” column uses the ISNUMBER function to check if the Completion (%) value is a numeric value:
- We use the VALUE function to convert the text representations of numbers to actual numeric values.
- If the value is numeric, the IF function checks if the task is 100% completed or not:
- If the job is 100% satisfied, the formula displays “Completed.”
- If the job is not 100% completed, the formula displays “In Progress.”
- If the value is not numeric, the formula shows “Invalid Data.”
🌼 Example 3: Inventory Check 🌼
Suppose we have a table with inventory data, and we want to check if the Quantity of each product is more significant than zero (in stock). We’ll use the ISNUMBER function in combination with an IF function to display the availability status.
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Product Name | Quantity |
| 2 | PROD001 | Pen | 50 |
| 3 | PROD002 | Not Available | |
| 4 | Notebook |
Explanation:
- We have a table with product data, including Product ID, Product Name, and Quantity.
- The “Quantity” column uses the ISNUMBER function to check if the Quantity value is numeric:
- We use the VALUE function to convert the text representations of numbers to actual numeric values.
- If the value is numeric, the IF function checks if the Quantity is more significant than zero:
- If the Amount exceeds zero, the formula displays “In Stock.”
- If the Amount is zero or blank, the recipe says “Out of Stock.”
- If the value is not numeric, the formula shows “Invalid Data.”
🌼 Example 4: Exam Grading Scheme 🌼
Suppose we have a table with student exam scores and want to apply a grading scheme based on the scores. We’ll use the ISNUMBER function and an IF function to assign grades.
| A | B | C | |
|---|---|---|---|
| 1 | Student ID | Name | Score |
| 2 | 101 | John Doe | 85 |
| 3 | 102 | Jane Smith | 95 |
| 4 | 103 | Bob Johnson | Not Available |
Explanation:
- We have a table with student data, including Student ID, Name, and Score.
- The “Score” column uses the ISNUMBER function to check if the Score value is numeric:
- If the value is numeric, the IF function checks the score against the grading scheme:
- If the score is 90 or higher, the formula assigns the grade “A.”
- If the score is between 80 and 89, the formula gives the rise “B.”
- If the score is between 70 and 79, the formula gives the grade “C.”
- If the score is between 60 and 69, the formula gives the grade “D.”
- If the score is below 60, the formula gives the grade “F.”
- If the value is not numeric, the formula displays “Invalid Data.”
- If the value is numeric, the IF function checks the score against the grading scheme:
🌼 Example 5: Sales Commission Calculation 🌼
Suppose we have a table with sales data and want to calculate the sales commission for each salesperson based on their sales performance. We’ll use the ISNUMBER function in combination with an IF function for commission calculation.
| A | B | C | |
|---|---|---|---|
| 1 | Salesperson | Sales Amount | Commission |
| 2 | John | $500 | =IF(ISNUMBER(VALUE(B2)), IF(VALUE(B2)>1000, B2*0.1, B2*0.05), "Invalid Data") |
| 3 | Jane | Not Available | =IF(ISNUMBER(VALUE(B3)), IF(VALUE(B3)>1000, B3*0.1, B3*0.05), "Invalid Data") |
| 4 | Bob | $1200 | =IF(ISNUMBER(VALUE(B4)), IF(VALUE(B4)>1000, B4*0.1, B4*0.05), "Invalid Data") |
Explanation:
- We have a table with sales data, including Salesperson and Sales Amount.
- The “Commission” column uses the ISNUMBER function to check if the Sales Amount is a numeric value:
- If the value is numeric, the IF function calculates the commission based on the sales performance:
- If the Sales Amount is more significant than $1000, the formula calculates a 10% commission.
- If the Sales Amount is $1000 or less, the formula calculates a 5% commission.
- If the value is not numeric, the formula displays “Invalid Data.”
- If the value is numeric, the IF function calculates the commission based on the sales performance:
🌼 Example 6: Inventory Cost Calculation 🌼
Suppose we have a table with inventory data, and we want to calculate the total cost of each product based on the quantity and unit price. We’ll use the ISNUMBER function in combination with the SUM function for cost calculation.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product Name | Quantity | Unit Price |
| 2 | PROD001 | Pen | 50 | $1.50 |
| 3 | PROD002 | Not Available | $2.00 | |
| 4 | Notebook | $3.00 |
Explanation:
- We have a table with product data, including Product ID, Product Name, Quantity, and Unit Price.
- The “Total Cost” column uses the ISNUMBER function to check if the Quantity is a numeric value:
- If the value is numeric, the SUM function calculates the total cost by multiplying the Quantity with the Unit Price.
- If the value is not numeric, the formula displays “Invalid Data.”
🌼 Example 7: Salary Increment Calculation 🌼
Suppose we have a table with employee data, and we want to calculate the salary increment for each employee based on their current salary and performance rating. We’ll use the ISNUMBER function in combination with an IF function for increment calculation.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee ID | Name | Salary | Rating | New Salary |
| 2 | 101 | John Doe | $50000 | 4.5 | =IF(ISNUMBER(VALUE(C2)), IF(ISNUMBER(VALUE(D2)), C2*(1+D2/100), "Invalid Data"), "Invalid Data") |
| 3 | 102 | Jane Smith | Not Available | 4.2 | =IF(ISNUMBER(VALUE(C3)), IF(ISNUMBER(VALUE(D3)), C3*(1+D3/100), "Invalid Data"), "Invalid Data") |
| 4 | 103 | Bob Johnson | $60000 | Not Available | =IF(ISNUMBER(VALUE(C4)), IF(ISNUMBER(VALUE(D4)), C4*(1+D4/100), "Invalid Data"), "Invalid Data") |
Explanation:
- We have a table with employee data, including Employee ID, Name, Salary, and Rating.
- The “New Salary” column uses the ISNUMBER function to check if the Salary and Rating are numeric values:
- If both values are numeric, the IF function calculates the new salary by adding the increment based on the rating (percentage of the current salary).
- If any value is not numeric, the formula displays “Invalid Data.”
🌼 Example 8: Data Validation 🌼
Suppose we have a table with various data types and want to validate whether each cell contains a numeric value. We’ll use the ISNUMBER function combined with an IF function to validate data.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item ID | Item Name | Quantity | Status |
| 2 | ITM001 | Pen | 100 | =IF(ISNUMBER(VALUE(C2)), "Valid", "Invalid") |
| 3 | ITM002 | Not Available | $50 | =IF(ISNUMBER(VALUE(C3)), "Valid", "Invalid") |
| 4 | ITM003 | Notebook | Not Available | =IF(ISNUMBER(VALUE(C4)), "Valid", "Invalid") |
Explanation:
- We have a table with item data, including Item ID, Item Name, Quantity, and Status.
- The “Status” column uses the ISNUMBER function to check if the Quantity value is numeric:
- IF THE VALUE IS NUMERIC, the IF function returns “Valid” for data validation.
- The formula returns “Invalid” for data validation if the value is not numeric.
🌼 Example 9: Conditional Summation 🌼
Suppose we have a table with expenses data and want to only calculate the total expenses for numeric values. We’ll use the ISNUMBER function in combination with the SUM function for conditional summation.
| A | B | C | |
|---|---|---|---|
| 1 | Expense ID | Category | Amount |
| 2 | EXP001 | Office | $200 |
| 3 | EXP002 | Not Available | $150 |
| 4 | EXP003 | Marketing | Not Available |
Explanation:
- We have a table with expenses data, including Expense ID, Category, and Amount.
- The “Total Expenses” column uses the ISNUMBER function to check if the Amount value is numeric:
- The SUM function adds the Amount to the total expenses if the numeric value.
- The formula adds zero to the total expenses if the value is not numeric.
🌼 Example 10: Data Cleaning 🌼
Suppose we have a table with sales data, and we want to clean the data by removing non-numeric entries in the “Units Sold” column. We’ll use the ISNUMBER function in combination with an IF function for data cleaning.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product Name | Units Sold | Status |
| 2 | PROD001 | Pen | 100 | =IF(ISNUMBER(VALUE(C2)), C2, "") |
| 3 | PROD002 | Not Available | 200 | =IF(ISNUMBER(VALUE(C3)), C3, "") |
| 4 | PROD003 | Notebook | Not Available | =IF(ISNUMBER(VALUE(C4)), C4, "") |
Explanation:
- We have a table with product sales data, including Product ID, Product Name, and Units Sold.
- The “Status” column uses the ISNUMBER function to check if the Units Sold value is numeric:
- If the value is numeric, the IF function retains the numeric value in the “Status” column.
- The formula leaves the cell blank in the “Status” column if the value is not numeric.
🌼 Example 11: Age Group Categorization 🌼
Suppose we have a table with the age of participants, and we want to categorize them into different age groups. We’ll combine the ISNUMBER function with an IF function for age group categorization.
| A | B | C | |
|---|---|---|---|
| 1 | Participant ID | Name | Age |
| 2 | P001 | John Doe | 30 |
| 3 | P002 | Jane Smith | Not Available |
| 4 | P003 | Bob Johnson | 50 |
Explanation:
- We have a table with participant data, including Participant ID, Name, and Age.
- The “Age Group” column uses the ISNUMBER function to check if the Age value is numeric:
- If the value is numeric, the IF function categorizes the participant into “Young” (age <= 20), “Middle-aged” (age <= 40), or “Senior” (age > 40).
- If the value is not numeric, the formula displays “Invalid Data.”
🌼 Example 12: Custom Function with ISNUMBER 🌼
Suppose we have a custom ” Discount ” function that calculates the discount based on the product category and price. We’ll use the ISNUMBER function to validate the input parameters in the custom function.
Function Discount(category As Variant, price As Variant) As Variant
If IsNumeric(price) Then
Select Case UCase(category)
Case "ELECTRONICS"
Discount = price * 0.1
Case "CLOTHING"
Discount = price * 0.2
Case Else
Discount = "Invalid Category"
End Select
Else
Discount = "Invalid Price"
End If
End Function Explanation:
- We have a custom function named “Discount” that takes two parameters: “category” and “price.”
- The Function uses the ISNUMBER function to check if the “price” parameter is a numeric value:
- If the “price” is numeric, the Function applies a discount based on the “category” parameter:
- If the category is “ELECTRONICS,” the Function applies a 10% discount.
- If the category is “CLOTHING,” the Function applies a 20% discount.
- For other categories, the Function returns “Invalid Category.”
- If the “price” is not numeric, the Function returns “Invalid Price.”
- If the “price” is numeric, the Function applies a discount based on the “category” parameter:
🌼 Part 3. Tips and Tricks 🌼
- The ISNUMBER function is a valuable tool for identifying numeric values in datasets and validating data.
- To create more complex logical expressions, you can combine the ISNUMBER function with other logical functions like IF, AND, OR, etc…
- Use conditional formatting to highlight cells containing numeric values based on the results of the ISNUMBER function.
- Be cautious when using the ISNUMBER function with cells containing dates, as dates are internally represented numeric values, and the Function will return TRUE for date cells.
- Always verify and test your formulas with various data scenarios to ensure accurate results.