Purpose
Return value
Syntax
=TYPE(value)
- value - The value to check the type of.
Using the TYPE function
The TYPE function returns a numeric code representing “type” in 5 categories: number = 1, text = 2, logical = 4, error = 16, and array = 64. The TYPE function takes one argument, value , which can be a reference, a formula, or a hardcoded value. The table below shows the possible type codes returned from TYPE and the meaning of each:
| Type code | Meaning |
|---|---|
| 1 | Number |
| 2 | Text |
| 4 | Logical value |
| 16 | Error value |
| 64 | Array |
| 128 | Compound data |
Examples
The TYPE function returns a numeric code:
=TYPE(100) // returns 1 for numbers
=TYPE("apple") // returns 2 for text
=TYPE(TRUE) // returns 4 for logicals
TYPE returns 16 for errors:
=TYPE(3/0) // returns 16
=TYPE(NA()) // returns 16
If TYPE is given an array constant , or a range , the result is 64:
=TYPE({1;2;3}) // returns 64
=TYPE(A1:C1 // returns 64
TYPE returns 128 for compound data, like LAMBDA functions:
=TYPE(LAMBDA(x,x*x)) // returns 128
Notes
- You can’t use TYPE to test for a formula, because TYPE evaluates the result.
- Excel dates and times are numeric values, and therefore return 1.
Purpose
Return value
Syntax
=ABS(number)
- number - The number to get the absolute value of.
Using the ABS function
The ABS function returns the absolute value of a number. You can think about absolute value as a number’s distance from zero on a number line. ABS converts negative numbers to positive numbers. Positive numbers and zero (0) are unaffected.
The ABS function takes just one argument , number , which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error.
Basic example
Negative numbers become positive, while positive numbers and zero (0) are unaffected:
=ABS(-3) // returns 3
=ABS(5) // returns 5
=ABS(0) // returns 0
Absolute Variance
Calculating the variance between two numbers is a common problem. For example, with a forecast value in A1 and an actual value in B1, you might calculate variance like this:
=B1-A1 // negative or positive result
When B1 is greater than A1, variance is a positive number. However, when A1 is greater than B1, the result will be negative. To ensure the result is a positive number, you can use ABS like this:
=ABS(B1-A1) // ensure positive result
See a detailed example here .
Counting absolute variances with conditions
The ABS function can be used together with the SUMPRODUCT function to count absolute variances that meet specific conditions. For example, to count absolute variances greater than 100, you can use a formula like this:
=SUMPRODUCT(--(ABS(variances)>100))
This formula is explained in more detail here .
Square root of negative number
The SQRT function calculates the square root of a number. If you give SQRT a negative number, it will return a #NUM! error:
=SQRT(-4) // returns #NUM!
To handle a negative number like a positive number, you can use the ABS function like this:
=SQRT(ABS(A1))
Calculating tolerance
To calculate whether a value is within tolerance or not, you can use a formula like this:
=IF(ABS(actual-expected)<=tolerance,"OK","Fail")
See a detailed explanation here .
