Support Board
Date/Time: Sun, 22 Dec 2024 16:37:14 +0000
[User Discussion] - help with spreadsheet formula
View Count: 1043
| [2015-02-09 22:36:02] |
| jivetrader - Posts: 410 |
|
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] |
| Sawtooth - Posts: 4141 |
|
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] |
| jivetrader - Posts: 410 |
|
thanks but i need to know the row number where the sequence changed.
|
| [2015-02-10 08:44:37] |
| jivetrader - Posts: 410 |
|
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: