Forum Discussion

StefanMeier's avatar
StefanMeier
Copper Contributor
May 18, 2022

Array function is not working anymore

Dear all I am using an array function that work so far, now it is not anymore, to be more precise, it is working in excel sheets that I made last year, using the same formula in the same excel sheet...
excel
office 365
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 18, 2022

StefanMeier See if the attached file works for you. In E4:F7 I used your formula, but since you tagged your post with Office365, you may want to use the Filter function as demonstrated in cell I4.

StefanMeier's avatar
StefanMeier
Copper Contributor
May 18, 2022
Thanks a lot, your solution is working perfect for me!

Even my formula is working in your sheet, but why?
    • StefanMeier's avatar
      StefanMeier
      Copper Contributor
      May 18, 2022
      Riny_van_Eekelen thanks a lot anyway , big help, it is really weird... Your sheet is now working with my formula as long I do not change the amount of cells.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 18, 2022

        StefanMeier 

        In your formula for all cells in the last part you use -ROW(A1). It looks like you re-enter formula, not drag it to the down.  If drag it down it'll be -ROW(A2), -ROW(A3), etc and you have correct result.

         

        As another comment, even in pre-DA Excel it's better to avoid array formulae, i.e. entered with CSE, and use functions which natively work with arrays. In this case that could be AGGREGATE, for example

        =INDEX($B$2:$B$11,  AGGREGATE(14,6, 1/($A$2:$A$11=$F$1)*(ROW($A$2:$A$11)-1),COUNTIF($A$2:$A$11,$F$1)+1-ROW(A1)))

        doesn't require CSE and returns correct result.

         

        In Excel with dynamic arrays formula with CSE returns first element of the array. You may check on =SEQUENCE(5) entered with Ctrl+Shift+Enter, it returns only one value with 1.

         

        Have no pre-DA Excel to test previous behaviour, perhaps I could be incorrect in details regarding first comment. But the point is the same - avoid CSE in old Excel.

Resources