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

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

  • 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

  • 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 ?

  • 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

Learn More

Forum Channels