Support Board

Date/Time: Tue, 14 May 2024 21:29:11 +0000

Post From: Spreadsheet for Trading, circular reference issue

[2021-06-14 03:52:05] Link To Thread - Link To Post [Go To First Post]
Sawtooth - Posts: 3996
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)