Do you actually use the TYPE function? If so, what do you use it for? Do you use it directly in the spreadsheet or in a macro?
Background to the question: The ODF TC has received a public comment on the TYPE function, stating that its specification is insufficient. I am working on this issue in the TC.
There is no function TYPE() in LibO Basic.
I never tried to use the Calc function TYPE() via a FunctionAccess object. I can try it if you are interested. Shall I?
Concerning the usage in spredsheets: See attachment.
disask_133056_ODF.ods (18.6 KB)
Possibly related
tdf#137667,
LibreOffice: SheetCell Service Reference.
I use the TYPE function in Excel and Calc formulas. My comments are in the attached file.
TypeFunction.ods (8.4 KB)
No, that would give only a hypothetical use.
My question does not refer to hypothetical use, but to actual use. Result 8 for a cell with formula is wrong behavior of LibreOffice, see tdf#73085. Further problems are described in tdf#171183 and tdf#171160. In addition the result in LibreOffice is different from Excel for a cell range as parameter. Because of all these problems, my question is whether this function is actually used at all.
I think, it is not related.
Actually? For what purpose? That is, what do you do with the results? Why do you use TYPE and not the ISFOO functions or the CELL function? Do cases where Excel and Calc are different, or where LibreOffice do not confirm to the standard, not occur in your use? Or how you handle such cases?
How else can we find out the type of a cell value (number, text, logical (Excel only), or error)?
The TYPE function is the most convenient way.
In Calc macros, we have to analyze getError(), CellContentType and FormulaResultType2.
We are aware of and take these differences into account when creating documents intended for both Calc and Excel (usually .xlsx, .xltx).
Excel knows Boolean as a third data type besides number and text. In Calc, TYPE returns boolean (4) if the cell has a constant number formatted as boolean.
Formula has precedence over value. =TRUE() returns 8 (formula), however, it remains unclear if the formula returns text, number or boolean.
I prefer the other info functions over TYPE.
In short now: I knew TYPE(), but I don’t use it due to the demonstrated inconistencies.
I continue to insist that Calc doesn’t have a Boolean cell value type.
One of the fundamental principles is that the value entered into a cell cannot change as a result of subsequent formatting of that cell.
Let’s conduct an experiment.
=TYPE(A1)
The formula displays 1.
3. Change the format of cell A1 to Boolean. Cell A1 displays TRUE (the localized value). Cell B1 displays 1.
4. Perform a full recalculation of the formulas. Cell B1 displays 4.
5. Change the cell format to “Standard.” Cell A1 displays the number 2 (it hasn’t changed), and the formula in B1 displays 4.
This indicates that the TYPE function in Calc is not working correctly. No function should take into account the cell format unless it is explicitly stated in its specification.
That’s true, but in Excel =1=TRUE() returns FALSE, which is an important difference. At this point, Calc’s TYPE function does some mimicry for compatibility when it reports a boolean value based on mere formatting.
The specification of TYPE has a note with a warning about this fact 6.13.33 TYPE.
There exist the functions ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISFORMULA and more.
The specification does not explicitly state, what the TYPE function has to return in case its parameter has data type ReferenceList (=operator union) or data type Reference with a reference that addresses not a single cell but a cell range, which might even be a cuboid. What return value would you want?
If the parameter is a range reference like B2:E16, should the TYPE function return 64 as if it were an array? Or would you prefer something else?
The parameter of the TYPE function has data type Any. Thus all of the above cases are allowed.
I would have thought that any array formula returns 64 (array). In fact, it returns 8 (formula). How do I get 64 with the current implementation?
For example =TYPE(COLUMN(B5:D5)) or if you have got a column B with numbers in B1:B20, then =TYPE(LARGE(B1:B20);{1;2}). These are examples where the function in the parameter of TYPE results an array. But the behavior is inconsistent. Not in all such cases a single value 64 is returned but for function MUNIT or an inline array, an array of values 64 is returned, for example.
Yes, the inline array I enter as an argument to TYPE is an array, of course. Why should I ever test this?
I believe that in such cases, Excel’s actual behavior (unless it contradicts a previously published standard) should be used as a guide. Since users are exchanging spreadsheets, it’s best to minimize the number of differences (as much as possible).
Thank you for your answers. To summarize: