Home >> Support Board

Support Board

Date/Time: Sat, 18 May 2024 11:37:28 +0000

How to Post a Message for Support

Offset formula in spreadsheet

View Count: 159

[2024-04-29 10:46:15] #1 [Go To First Post]
User440951 - Posts: 40
Hello everyone, I have a problem with a spreadsheet, here are the formulas in the different columns and ID2.SG13 corresponds to the total volume of the bar. K: Period variable (time) =AND(FRACTIME(A3)>=TIME(9,30,0),FRACTIME(A3)<TIME(9,59,59)) L: Period variable (time) =AND(FRACTIME(A3)>=TIME(15,30,0),FRACTIME(A3)<TIME(16,59,59)) M: Maximum volume calculation period 9:30 a.m.-10:00 a.m. =IF(K3=1,MAX(ID2.SG13@3:ID2.SG13@62),0) M: Maximum volume calculation period 3:30 p.m.-4:00 p.m. =IF(L3=1,MAX(ID2.SG13@3:ID2.SG13@62),0) My chart is set in a session 9:30 a.m. - 9:59:59 a.m. and with an evening session 3:30 p.m. - 3:59:59 p.m. (see capture). My goal is to obtain the maximum volume between 9:30 a.m. and 10 a.m. on one column and on the other between 3:30 p.m. and 4:00 p.m. The problem is that there is a gap, it takes into account the previous candle. That is to say that here on column M (bottom), the maximum volume between 9:30 and 10:00 should be 7401 and not 8299. I don't understand where the problem could come from. Someone would have any idea ?


2024-04-29 11_39_22.png / V - Attached On 2024-04-29 10:46:00 UTC - Size: 178.91 KB - 21 views

[2024-04-29 17:36:18] #2 [Go To First Post]
John - SC Support - Posts: 31704
The problem is that you are using relative references to the cells. So for Row 3 everything works fine, but for Row 4 the calculation in M4 would be the following: =IF(K4=1,MAX(ID2.SG13@4:ID2.SG13@63),0) And so on, so you are not viewing just the data from the one period of time. You would have to change these to be absolute references in order to get the correct data. So you would want to enter the following for M3 (for example): =IF(K$3=1,MAX(ID2.SG13@$3:ID2.SG13@$62),0) For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2024-04-29 18:52:05] #3 [Go To First Post]
User440951 - Posts: 40
Thank you for the feedback. I understand the logic and tried your formula. Unfortunately, the result is equally improbable and does not correspond to my objective. I put in M3: =IF(K$3=1,MAX(ID2.SG13@$3:ID2.SG13@$62),0) And in N3: =IF(L$3=1,MAX(ID2.SG13@$3:ID2.SG13@$62),0)


2024-04-29 20_50_07.png / V - Attached On 2024-04-29 18:50:36 UTC - Size: 172.38 KB - 14 views

[2024-04-29 21:46:04] #4 [Go To First Post]
Sawtooth - Posts: 4000
You could modify this example to get what you want: https://www.sawtoothtrade.com/example-10.html

[2024-04-30 15:55:46] #5 [Go To First Post]
User440951 - Posts: 40
Thanks for your feedback. I followed the example and put the following elements : Y3 : =IF(INT(A3)>INT(A4),Y4+1,Y4) Z3 : =IF(Y3=MAX(Y$3:Y$1002),A3-INT(A3),0) H3 : 09:30:00 H4 : 09:59:59 (Even if you put 10:00:00) H5 : =MAX(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1)1)) But it doesn't work, it puts me #SYNTAX! ...Even with SUM instead of MAX, as in the example. I also tried putting AA instead of F. Date Time Of Last Edit: 2024-04-30 15:56:52


2024-04-30 17_42_10.png / V - Attached On 2024-04-30 15:55:35 UTC - Size: 129.99 KB - 16 views

[2024-04-30 17:16:32] #6 [Go To First Post]
Sawtooth - Posts: 4000
Your H5 formula is missing a comma; use this instead: =MAX(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1)) Also, the current Z3 formula returns a #CREF!, so do this instead: Put this in Y1: =MAX(Y3:Y1002) Then use this formula in Z3: =IF(Y3=$Y$1,FRACTIME(A3),0) Also, the time values in H3/H4 need to exist in the Z3:Z1002 range. This might not happen if the bar duration is very short, because the Number of Rows might not include enough range. [Note: https://www.sawtoothtrade.com/example-10.html updated.] Date Time Of Last Edit: 2024-04-30 17:26:47

[2024-04-30 20:22:18] #7 [Go To First Post]
User440951 - Posts: 40
Thank you so much ! So I put these elements : Y1 : =MAX(Y3:Y1002) Y3 : =IF(INT(A3)>INT(A4),Y4+1,Y4) Z3 : =IF(Y3=$Y$1,FRACTIME(A3),0) H3 : 09:30:00 H4 : 09:59:00 H5 : =MAX(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1)) H3 : 09:30:00 H4 : 09:59:00 H5 : =MAX(INDEX(F$3:F$1002,MATCH($H$7,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$6,Z$3:Z$1002,-1),1)) Everything works but there is still one small point that I would like to improve. Indeed, I need these calculations for the previous day's session, and the problem is that if I only have one bar from today's session (9:30 a.m. for example) it gives me displayed in H5 #N/A as in H8 And as soon as all the bars from 9:30 a.m. to 10:00 a.m. are closed in H5 I have the correct maximum volume and in H8 always #N/A. I understand the logic of this problem but I don't see how to recover the calculation from the previous session and ignore the current day. I guess you need to include a day -1 variable but how can I do that ? Date Time Of Last Edit: 2024-04-30 20:36:18

[2024-04-30 22:09:16] #8 [Go To First Post]
Sawtooth - Posts: 4000
I guess you need to include a day -1 variable but how can I do that ? You'd need to use, e.g., cell X3 with this formula: =IF(Y3=$Y$1-1,FRACTIME(A3),0) then this formula in H8: =MAX(INDEX(F$3:F$1002,MATCH($H$7,X$3:X$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$6,X$3:X$1002,-1),1))

[2024-05-01 09:19:21] #9 [Go To First Post]
User440951 - Posts: 40
Thank you so much ! It works perfectly well :D

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