Forum Discussion
Looking for use cases of function TYPE
What do you use the TYPE function for? I’m not looking for a general explanation of what the TYPE function can do—I already know that. For example, why do you use the TYPE function instead of functions like ISTEXT, ISERROR, etc.? Or, how do you interpret the result of the TYPE function?
4 Replies
HelloRegina_Henschel,
I mainly use the TYPE function when one formula needs to react differently depending on the returned value type. TYPE returns a numeric code identifying the result type, so a single test can replace several checks such as ISTEXT, ISNUMBER, or ISERROR.
It is also useful to detect arrays. TYPE returns 64 when a formula evaluates to an array, which helps when working with dynamic array formulas that may sometimes return a single value and sometimes a spill range.
Another practical use is debugging complex formulas. TYPE(formula) quickly shows whether the result is a number, text, logical value, error, or array.
Documentation
https://support.microsoft.com/en-us/office/type-function-45b4e688-4bc3-48b3-a105-ffa892995899TYPE is helpful for two things mostly:
64 - array detection (can also use ROWS and COLUMNS for dimension check)
128 - compound data
This is a basic example of using TYPE to validate the function input of a Lambda:
MyLambda= LAMBDA( array, [function], LET( // Check optional function input, default to SUM if omitted function, IF(ISOMITTED(function),SUM,function), // Compound data - TYPE = 128 - valid function InValidFn?, TYPE(function) <> 128, // Halt function if supplied function is invalid IF(InValidFn?,"#INVALID-FUNCTION!",function(array)))); // Sheet level formula: =MyLambda(arr,AVERAGE)So you do not use TYPE directly in the sheet, but inside a named expression that is intended to be used by someone else?
That's the short of it.
The ability to supply functions like this ETA arrangement for example:
=BYROW(array,SUM)Is a recent change from how things have been for a long time. This same ETA arrangement can be used in Lambdas and that's where TYPE comes in to check the input.
