Find lowest value in a column between 2 cells and calculate again for next sample

[gotaquestion1]

Hi,

Please see spreadsheet attached.

In column D if a new high is made I wish to find the Low from C between the old new high and the new new high of D.

My current way of calculating is to enter 1 in column E for the new high. Then filter column E for only values of 1. Then I double click on column F and drag the cell selector to the appropriate columns of column C.

Is there a more elegant less time consuming way of doing this?

Thank you for your help.

[RaulSerg]

Does the attached help?

[WideBoyDixon]

Try this in F2:

Please Login or Register  to view this content.

Then copy it down.

WBD

[davsth]

I think similar to the above
=IFERROR(IF(E2=1,MIN(OFFSET(C1,0,0,-(1+ROW(D1)-LOOKUP(2,1/($E1:E$2=1),ROW($E1:E$2))),1)),""),"")

corrected as the below post, but as pointed out the index function is not volatile so a better choice

[RaulSerg]

I think similar to the above
=IFERROR(IF(E2=1,MIN(OFFSET(C1,0,0,-(ROW(#REF!)-LOOKUP(2,1/($E1:E$2=1),ROW($E1:E$2))),1)),""),"")

davsth, did you delete something before pasting the formula, probably manual calculation on?

[WideBoyDixon]

FWIW, any formula with OFFSET in it is volatile and will recalculate when anything changes. Just be aware ...

WBD

[gotaquestion1]

Thanks, the answers are what I was looking for. I don't fully understand the formulas, I'll have a look at them and come back with questions if that's ok. Thanks again.

[WideBoyDixon]

Another option:

Please Login or Register  to view this content.

Taking it apart:

Please Login or Register  to view this content.

If E2 is zero then leave this cell blank.

Please Login or Register  to view this content.

Find the row on which the previous highest value occurred.

Please Login or Register  to view this content.

Locate the correct cell in column C based on the row value we found.

Please Login or Register  to view this content.

Find the minimum value from column C based on the cell previously identified and the cell in column C on the current row.

Hope that helps your understanding. It's a bit easier to explain than the LOOKUP(2,1/...) stuff.

WBD

[gotaquestion1]

Thanks for the updated formula and explanation. Yes I understand that formula much better. What does the 0 do at the end of:

MATCH($D1,$B:$B,0)

Thanks

[WideBoyDixon]

MATCH(..,..,0) means "find an exact match"

WBD