I am looking at stock-data at 1 minute resolution in a spreadsheet. I have been stuck at this problem for a few days now, testing different methods.

What I am doing is that I have formulas dictating entries and sells based on the stock-data, and then I have 2 existing formulas that finds the highest/lowest price the stock reached inbetween entry and sell minutes. So far, so good, now I need to find at which minute (= row) this highest and lowest price occurred. I have some versions that I tested that works when I have only 1 entry and sell, but it gets messed up when I have several entries and sells.

Added a screenshot for sample data. This data should be read from BOTTOM and UP. First column is = 1 if a trade is active. Second column keeps track of the highest value that has occurred for the active trade and is reset for next entry. Third column keeps track of the lowest value that has occurred for the active trade and is reset for the next entry.

I need help with formulas in column 4-5 to find WHEN the highest value occurred in the active trades and the lowest value respectively.

There could be cases where I have more than 2 entries as well. Current formula in column 4: =IF(AND(B4>B5,B3<=B4),%%time of row from other cell here%%,MISSING()) as you can see, for the 2nd entry I have 2 cases when this formula gives "true", which is faulty. Only the one showing as 09:54 should be there.

The formula for column 5 is very bad, it's written with MIN-if combo but I know this won't work in many cases, so I omit it here.

Edit:

CodePudding user response：

maxby a bit overkill, but how about:

=LET(range,A2:C21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,3),
     DROP(REDUCE(0,trade,
          LAMBDA(x,y,
          VSTACK(x,
                 IF(y=0,
                    "",
                    LET(sq,SEQUENCE(COUNTA(trade)),
                        start,XLOOKUP(1,(trade=0)*(sq 1<=ROW(y)),sq 2,"",0,-1),
                        end,XMATCH(1,(INDEX(trade,sq)=0)*(sq 1>=ROW(y))),
                        sqsingle,SEQUENCE(1 end-start,,start-1),
                        singlevalue,INDEX(value,sqsingle),
                        singlemax,XLOOKUP(MAX(singlevalue),singlevalue,sqsingle,,0,-1),
                        INDEX(time,singlemax)))))),
     1))

It isolates the range of a single trade and looks for the position of the max value in that range (bottom to top) and returns the (date &) time.

alternative solution without REDUCE() to be used in row 2 and needs dragged down:

=LET(range,$A$2:$C$21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,3),
     start,XLOOKUP(0,B$2:B2,SEQUENCE(COUNTA(B$2:B2)) 1,,0,-1),
     end,XLOOKUP(1,(trade=0)*(ROW(trade)>=ROW()),SEQUENCE(COUNTA(trade))-1),
     sqsingle,SEQUENCE(1 end-start,,start),
     singlevalue,INDEX(value,sqsingle),
     singlemax,XLOOKUP(MAX(singlevalue),singlevalue,sqsingle,,0,-1),
     IF(B2=0,"",INDEX(time,singlemax)))

And for MIN value:

=LET(range,$A$2:$D$21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,4),
     start,XLOOKUP(0,B$2:B2,SEQUENCE(COUNTA(B$2:B2)) 1,,0,-1),
     end,XLOOKUP(1,(trade=0)*(ROW(trade)>=ROW()),SEQUENCE(COUNTA(trade))-1),
     sqsingle,SEQUENCE(1 end-start,,start),
     singlevalue,INDEX(value,sqsingle),
     singlemax,XLOOKUP(MIN(singlevalue),singlevalue,sqsingle,,0,-1),
     IF(B2=0,"",INDEX(time,singlemax)))