How to find the maximum value of the last 6 consecutive days without spaces?

[rayhen]

B2:AF2 , the last 6 consecutive days as 18790.425 19480.144 17561.242 22241.642 21808.236 22056.135
Place the maximum value in cell A2
B3:AF3 , the last 6 consecutive days as 34507.729 31460.845 30667.831 31876.037 34445.296 34690.466
Place the maximum value in cell A3
B4:AF4 , the last 6 consecutive days as 19 3 5 15 12 18
Place the maximum value in cell A4

Any help would be appreciated!

[KokiKoki]

At A2, try this with CSE

=IFERROR(MAX(--((COLUMN($B:$AF)-1)<=LARGE(IF(MID(CONCAT(--NOT(ISBLANK($B2:$AF2))),ROW($A$1:$A$20),6)="111111",ROW($A$1:$A$20),""),1)+5)*--((COLUMN($B:$AF)-1)>=(LARGE(IF(MID(CONCAT(--NOT(ISBLANK($B2:$AF2))),ROW($A$1:$A$20),6)="111111",ROW($A$1:$A$20),""),1)))*$B2:$AF2),"Not Available")

[BMV]

there is volatile array function for 2003-2007

Please Login or Register  to view this content.

or
volatile non array for 2013 ...

Please Login or Register  to view this content.

array but not volatile with MMULT in the XLSX attachment

[rayhen]

#2 The result here is Available
#3 BMV you are too good, only the first formula can be used, the second one is not

Both Much appreciated!

[BMV]

Long? non array but not volatile option for 2003

Please Login or Register  to view this content.

[wk9128]

Try A2 cell , Array formula , drag down

[BMV]

@wk9128 really good option. I've newer use lookup with 2d array .
One remark , the formula work only if one of 6 values positive. This is restriction.
and it could shorter

Please Login or Register  to view this content.

or decrease calculation array

Please Login or Register  to view this content.

[wk9128]

BMV You also performed very well, come on together

[rayhen]

wk9128 and BMV , Both are too good, you can use functions as magic, and there are many changes

[wk9128]

You're Welcome and Thank You for the feedback