Support Array type

[wojciechczerniak]

Description

ODFF defines Inline Arrays, Excel has Array Formulas/Constants.

• 5.13 Inline Arrays (Array Constants) defines the syntax for storing an array: {1;2|3;4} where ; is a column separator (, in Excel) and | is a row separator (; in Excel). IMO both should be a configuration options #58
• This creates a new type 4.10 Array
• And has it's own evaluation logic 3.3 Array Expressions
• Already supported by the matrix funtions: MINVERSE, MMULT, MUNIT, TRANSPOSE, FREQUENCY, GROWTH, LINEST, LOGEST, TREND

Values rules

Array constants can contain numbers, text, logical values (such as TRUE and FALSE), and error values such as #N/A. You can use numbers in integer, decimal, and scientific formats. If you include text, you need to surround it with quotation marks ("text”).

Array constants can't contain additional arrays, formulas, or functions. In other words, they can contain only text or numbers that are separated by commas or semicolons. Excel displays a warning message when you enter a formula such as {1,2,A1:D4} or {1,2,SUM(Q2:Z8)}. Also, numeric values can't contain percent signs, dollar signs, commas, or parentheses.

Examples

• ={1;2|3;4} will result in:

1	2
3	4

• Multiple each item in an array =SEQUENCE(1,12)*2 or ={1,2,3,4;5,6,7,8;9,10,11,12}*2. You can also divide with (/), add with (+), and subtract with (-).
• Square the items in an array =SEQUENCE(1,12)^2 or ={1,2,3,4;5,6,7,8;9,10,11,12}^2
• Find the square root of squared items in an array=SQRT(SEQUENCE(1,12)^2) or =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)
• Transpose a one-dimensional row =TRANSPOSE(SEQUENCE(1,5)) or =TRANSPOSE({1,2,3,4,5}). Even though you entered a horizontal array constant, the TRANSPOSE function converts the array constant into a column.
• Transpose a one-dimensional column =TRANSPOSE(SEQUENCE(5,1)) or =TRANSPOSE({1;2;3;4;5}). Even though you entered a vertical array constant, the TRANSPOSE function converts the constant into a row.
• Transpose a two-dimensional constant =TRANSPOSE(SEQUENCE(3,4)) or =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12}). The TRANSPOSE function converts each row into a series of columns.
• =SUM(F10:F19*G10:G19) or =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19)

Naming Inline Arrays (Array Formulas/Constants)

This can be done with Named Expressions API #126

hf.addNamedExpression('Quater1', { reference: '={"January"; "February"; "March"}' })
hf.addNamedExpression('Quater2', { reference: '={"April"; "May"; "June"}' })

Rember to use it with = sign: =Quarter1. Otherwise Quarter1 should be parsed as text.

Evaluation

1. Evaluation as an implicit intersection of the argument with the expression's evaluation position.
2. Matrix evaluation.

SPILL Error

Until the blockage is removed. Then everything should be displayed as normal.

Links

ODFF: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017968_715980110 ODFF: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017908_715980110 ODFF: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017866_715980110 Array formulas: https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 Dynamic vs constant arrays: https://support.office.com/en-us/article/dynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4 Using arrays: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Using_Arrays

[izulin]

671 introduces arrays parsed in formulas

[wojciechczerniak]

@izulin Not the same. This task is about arrays defined in the standard: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017866_715980110

[izulin]

@izulin Not the same. This task is about arrays defined in the standard: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017866_715980110

Could you point to feature from the standard that is not implemented?

[wojciechczerniak]

AFAIK you can't just type in the formula: = {1} + {1;2;3|4;5;6} and get value {2;3;4|5;6;7} as described in the use cases.

The notation was removed?

[izulin]

You can with #671

What do you mean by removed?

[wojciechczerniak]

Ok, didn't noticed that. I've seen tests with that notation removed in previous PRs, like ={TRANSPOSE( ... )}.

If you say we're here then I will definitely check #671 and the spec more carefully again ❤️

[izulin]

Removal of old ={TRANSPOSE( ... )} was step towards supporting this (modern) array notation.

[wojciechczerniak]

Done with so many PR that I can't list them here. See changelog for v1.