Support Board
Date/Time: Thu, 27 Feb 2025 03:13:02 +0000
Post From: Spreadsheet for Trading, circular reference issue
| [2021-06-14 03:52:05] |
| Sawtooth - Posts: 4176 |
|
Some ideas: - You can use TIMEVALUE("16:00:00") to get the decimal value of 16:00:00 - Both value area lines studies have Reference Days Back options to get yesterday's values. - You can use a formula like this to bring yesterday's VAH or VAL at 16:00 to row 3, e.g. in cell R3: =IF(AND(FRACTIME(A3)>=TIMEVALUE("16:00:00"),FRACTIME(A4)<TIMEVALUE("16:00:00")),ID1.SG2@3,R4) where ID1.SG2 is the VAH. Note: So that you can insert/delete columns and rearrange studies in the Studies to Graph list without dislocating references, it's best to use this method to reference a study's output: Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study - You could use the GETCORRESPONDINGMATCH function instead of VLOOKUP, because it has a Nearest option. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#GetCorrespondingMatch_Function - To avoid a #CREF error, you can include the referenced cell's formula in the referencing formula, instead of a direct cell reference. Yes, resolving these can be tricky and cumbersome. - If the result is boolean (T/F) you don't need to use the IF function: =AND(FRACTIME(A3)=0.39583333621703787, B3<X3, B3>Y3) |