Support Board
Date/Time: Mon, 03 Feb 2025 20:02:43 +0000
Post From: Spreadsheet
| [2019-07-12 02:24:48] |
| Sawtooth - Posts: 4165 |
|
I need to break 24 hours into 3 zones to track the volume.
- You must use an absolute reference to J41 with formulas in Formula Columns:FRACTIME($J$41) - Column G is # of Trades, not Volume. - You'll want a time inclusive formula for each, instead of a time exclusive formula: US TRADES R3: =AND(FRACTIME($J$41)>TIMEVALUE("08:29:01"),FRACTIME($J$41)<TIMEVALUE("15:54:55"),G3>1500) EU TRADES S3: =AND(FRACTIME($J$41)>TIMEVALUE("00:00:00"),FRACTIME($J$41)<TIMEVALUE("08:29:00"),G3>400) ASIA TRADES T3: =AND(FRACTIME($J$41)>TIMEVALUE("17:00:00"),FRACTIME($J$41)<TIMEVALUE("23:59:59"),G3>400) - Since you are using Formula Columns, you might want to use the time of each bar in A3, instead of J41: US TRADES R3: =AND(FRACTIME(A3)>TIMEVALUE("08:29:01"),FRACTIME(A3)<TIMEVALUE("15:54:55"),G3>1500) EU TRADES S3: =AND(FRACTIME(A3)>TIMEVALUE("00:00:00"),FRACTIME(A3)<TIMEVALUE("08:29:00"),G3>400) ASIA TRADES T3: =AND(FRACTIME(A3)>TIMEVALUE("17:00:00"),FRACTIME(A3)<TIMEVALUE("23:59:59"),G3>400) - You could use column H for the times and # of trades, then reference these cells in the formulas: H1: US start; end; trades H2: 08:29:01 H3: 15:54:55 H4: 1500 H5: EU start; end; trades H6: 00:00:00 H7: 08:29:00 H8: 400 H9: ASIA start; end; trades H10: 17:00:00 H11: 23:59:59 H12: 400 R3: =AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4) S3: =AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8) T3: =AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12) - You could combine all three in one column: =OR(AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4),AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8),AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12)) |