The TYPE function in Excel is a useful tool to check the data type of a value or a cell reference. It returns a numeric code that corresponds to the type of the value, such as 1 for numbers, 2 for text, 4 for logical values, 16 for error values, and so on. You can use the TYPE function to validate the inputs of a formula, to avoid errors or unexpected results.
The syntax of the TYPE function is:
=TYPE(value)
where value is the value or cell reference that you want to check the data type of.
The TYPE function returns one of the following numeric codes:
| Code | Data Type |
|---|---|
| 1 | Number |
| 2 | Text |
| 4 | Logical (TRUE or FALSE) |
| 8 | Cell reference |
| 16 | Error value |
| 64 | Array |
For example, if you have the value 100 in cell A1, the formula =TYPE(A1) will return 1, indicating that it is a number. If you have the text “Hello” in cell B1, the formula =TYPE(B1) will return 2, indicating that it is text. If you have the formula =SUM(A1:B1) in cell C1, the formula =TYPE(C1) will return 8, indicating that it is a cell reference.
Procedures
To use the TYPE function to check for invalid values in a formula, you can follow these steps:
- Identify the inputs of the formula that you want to validate, and the expected data type of each input.
- Use the TYPE function to check the data type of each input, and compare it with the expected data type.
- Use the IF function or other logical functions to return an appropriate message or action if the data type does not match the expected data type.
Example
Suppose you have a formula that calculates the average of three numbers in cells A1, A2, and A3. The expected data type of each input is a number. You can use the TYPE function to check if any of the inputs is not a number, and return an error message if so. The formula is:
=IF(OR(TYPE(A1)<>1,TYPE(A2)<>1,TYPE(A3)<>1),"Invalid input",AVERAGE(A1:A2:A3))
This formula checks if the data type of A1, A2, or A3 is not equal to 1, which means they are not numbers. If any of them is not a number, the formula returns “Invalid input”. Otherwise, it returns the average of the three numbers.
Here is an example of how the formula works in an Excel table:
| A | B |
|---|---|
| 10 | =IF(OR(TYPE(A1)<>1,TYPE(A2)<>1,TYPE(A3)<>1),“Invalid input”,AVERAGE(A1:A2:A3)) |
| 20 | 15 |
| 30 |
The formula in cell B1 returns 15, which is the average of 10, 20, and 30. However, if you change any of the values in column A to a text or an error value, the formula in cell B1 will return “Invalid input”.
Other Approaches
There are other ways to check for invalid values in a formula, such as using the ISNUMBER, ISTEXT, ISERROR, or other IS functions. These functions return TRUE or FALSE depending on the data type of the value. For example, the ISNUMBER function returns TRUE if the value is a number, and FALSE otherwise. You can use these functions in combination with the IF function or other logical functions to validate the inputs of a formula.
For example, the formula that calculates the average of three numbers in cells A1, A2, and A3 can be rewritten as:
=IF(AND(ISNUMBER(A1),ISNUMBER(A2),ISNUMBER(A3)),AVERAGE(A1:A2:A3),"Invalid input")
This formula checks if all the values in A1, A2, and A3 are numbers using the ISNUMBER function. If they are all numbers, the formula returns the average of the three numbers. Otherwise, it returns “Invalid input”.
