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