Find maximum values before a zero in excel-CodePudding

I'm trying to use the formula =MAX(FREQUENCY(IF(T2:AC2>1,ROW(T2:AC2)),IF(T2:AC21>=1,ROW(T2:AC2))))to find more than one maximum value before a zero from a range.

ROW has this values

1 2 0 1 2 3 4 5 6 0

I want a formula/formulas that will return 2 and 6

Thank you

CodePudding user response：

You can use this formula

=LET(list,
   FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(CONCAT(A1:A16),"d","l"),"l","</s><s>")&"</s></t>","//s"),
   LEN(list)-LEN(SUBSTITUTE(list,"w","")))

CodePudding user response：

Got this working =IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$3:R$100)/((R$3:R$1100=0)*(R$2:R99<>0)),ROWS(S$3:S3))-1),"")

assuming the data is in column R, put the formula in column S Link:https://www.mrexcel.com/board/threads/find-more-than-one-maximum-value-before-a-zero.1188659/post-5793250.