Filled max and min values in running column cells

[thilag]

Hello Friends

Please find the attached 2010 version excel file <Filled max and min values in running 280415.xlsx>.

I want to plot the max. high values and min. low values in the light yellow color cells by formula.

thanks

thilag.

[thilag]

Please ignore the date mismatch in the filename.

[samba_ravi]

F2=IF($G1=$G2,$F1,IF(G2="Low",MIN(INDEX($B2:$B$21,MATCH($G2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($G2="Low","High","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21)))),MAX(INDEX($B2:$B$21,MATCH($G2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($G2="Low","High","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))))))

E2=IF($G1=$G2,$E1,INDEX(INDEX($A2:$A$21,MATCH($G2,$C2:$C$21,0)):INDEX($A2:$A$21,IFERROR(MATCH(IF($G2="Low","High","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),MATCH(F2,INDEX($B2:$B$21,MATCH($G2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($G2="Low","High","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),0)))
TRY THIS AND COPY TOWARDS DOWN

[thilag]

Hello Siva

thanks for your formulas.

I made some minor changes in your formulas.

E2=IF($C1=$C2,$E1,INDEX(INDEX($A2:$A$21,MATCH($C2,$C2:$C$21,0)):INDEX($A2:$A$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),MATCH(F2,INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),0)))

F2=IF($C1=$C2,$F1,IF(C2="Low",MIN(INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21)))),MAX(INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))))))

Array formula in G2
G2=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))

Still the formulas is shows error in Cells E12:F13.

But i hope in my real application it will not happen.

thanks again

thilag

[samba_ravi]

Hello Siva

thanks for your formulas.

I made some minor changes in your formulas.

E2=IF($C1=$C2,$E1,INDEX(INDEX($A2:$A$21,MATCH($C2,$C2:$C$21,0)):INDEX($A2:$A$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),MATCH(F2,INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))),0)))

F2=IF($C1=$C2,$F1,IF(C2="Low",MIN(INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21)))),MAX(INDEX($B2:$B$21,MATCH($C2,$C2:$C$21,0)):INDEX($B2:$B$21,IFERROR(MATCH(IF($C2="Low","HiCh","Low"),$C2:$C$21,0)-1,ROWS(C2:C$21))))))

Array formula in G2
G2=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))

Still the formulas is shows error in Cells E12:F13.

But i hope in my real application it will not happen.

thanks again

thilag

G2=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0)) instead of the this use

G2=C2
and Change "HiCh" as "High" in your formula