## How to apply these Excel's Array Functions in Spread.

Posted by: zhenghq on 8 September 2017, 2:48 pm EST

• Post Options:

Posted 8 September 2017, 2:48 pm EST

=OFFSET(A1,COUNT(A:A)-1,)

=OFFSET(A1,MAX((A1:A200=0)*(A1:A200<>"")*ROW(1:200))-2,)

=OFFSET(A1,LARGE((A1:A200=0)*(A1:A200<>"")*ROW(1:200),2)-2,)

the above formulas are used in Excel

How to apply these composite formulas in Spread.

FpSpread1.Sheets(0).Cells(1, 42).Formula = "OFFSET(A1,COUNT(A:A)-1,)"    the return value is  #NA!

Help me,

Much appreciate

Kerry

• Post Options:

Replied 8 September 2017, 2:48 pm EST

for example, the datalist is the following

0
1
2
3
4      NO.4
0      NO.3
0
1
2
3
4
5
6       NO.2
0
1
2       NO.1

in Excel

NO.1   =OFFSET(A1,COUNT(A:A)-1,)        the return value is 2

NO.2    =OFFSET(A1,MAX((A1:A200=0)*(A1:A200<>"")*ROW(1:200))-2,)     the return value is 6

NO.3    =OFFSET(A1,LARGE((A1:A200=0)*(A1:A200<>"")*ROW(1:200),2)-2,)    the return value is 0

NO.4    =OFFSET(A1,LARGE((A1:A200=0)*(A1:A200<>"")*ROW(1:200),2)-3,)    the return value is 4

How to apply these Array Functions in spread ?

• Post Options:

Replied 8 September 2017, 2:48 pm EST

zheng_hq,

Excel supports array formulas. When an operator/function expecting an individual value as a parameter is passed an array value as a parameter, the operator/function gets applied to each element in the array value. The evaluation produces an array of results. In your example, the expression A1:A200=0 would evaluate to the array {A1=0;A2=0;...;A200=0}.

Spread does not support array formlas. when an operator/function expecting an individual value as a parameter is passed an array value as a parameter, the operator/function evaluates to a #VALUE! error. In your example, the expression A1:A200=0 would evaluate to the #VALUE! error value.

##### Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server