Home >> Support Board

Support Board

Date/Time: Thu, 02 May 2024 23:26:00 +0000

How to Post a Message for Support

[User Discussion] - help with spreadsheet formula

View Count: 964

[2015-02-09 22:36:02] #1 [Go To First Post]
jivetrader - Posts: 386
using SC spreadsheets (not Excel) let's say column K has an unknown number of rows of "TRUE" followed by an unknown number of rows of "FALSE", and this repeats ad infinitum. e.g. TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE . . . how to find the row of the most recent FALSE (row 6 in the above example)? TIA

[2015-02-10 00:14:36] #2 [Go To First Post]
Sawtooth - Posts: 3992
You won't know the final FALSE until there is an initial TRUE. This will go TRUE at only the first TRUE: =AND(K3,K4=FALSE)

[2015-02-10 06:01:54] #3 [Go To First Post]
jivetrader - Posts: 386
thanks but i need to know the row number where the sequence changed.

[2015-02-10 08:44:37] #4 [Go To First Post]
jivetrader - Posts: 386
i ended up doing nested IFs kind of like this =IF(AND($K3=TRUE,$K4=FALSE),$E4,IF(AND($K3=TRUE,T3>T4),$E3,IF($K3=TRUE,V4,0))) if we are at a crossover point (current row 3 is true and last row 4 was false) then E4 (previous LAST price). furthermore if current row is true (and last row was also true, because it wasn't false, right?) and indicator in column T is increasing, then E3 (current last price). if however current row is still true but the indicator isn't increasing, just give me the previous value in this column (V4). else (i.e. if current row is false) give me zero. a little crazy but working well. Date Time Of Last Edit: 2015-02-10 08:45:39

To post a message in this thread, you need to log in with your Sierra Chart account: