Home >> Support Board

Support Board

Date/Time: Fri, 03 May 2024 11:14:25 +0000

How to Post a Message for Support

[User Discussion] - Speadsheet study issue

View Count: 1155

[2014-10-24 14:15:30] #1 [Go To First Post]
User48557 - Posts: 138
I have this formula =IF(AND(WEEKDAY(A3)=1,A4-INT(A4)<TIMEVALUE("20:00:00"),A3-INT(A3)>=TIMEVALUE("20:00:00")),B3,K4) It draws a horizontal line when the Cotton market opens on Sunday at 10 pm, I am trying to tweak this formula so it works on the Cocoa Monday open at 3:45 am, but i am having trouble. It is not working Any suggestions for a formula that works?

[2014-10-24 15:21:17] #2 [Go To First Post]
Sawtooth - Posts: 3992
By default, the WEEKDAY function input for Sunday is 1. Use 2 for Monday. =IF(AND(WEEKDAY(A3)=2,A4-INT(A4)<TIMEVALUE("03:45:00"),A3-INT(A3)>=TIMEVALUE("03:45:00")),B3,K4) http://office.microsoft.com/en-us/excel-help/weekday-HP005209336.aspx?CTT=5&origin=HP005204211

[2014-10-24 18:23:47] #3 [Go To First Post]
User48557 - Posts: 138
that formula is not working for me.here is a screen shot of my settings


spreadsheet example.PNG / V - Attached On 2014-10-24 18:23:09 UTC - Size: 199.94 KB - 323 views Attachment Deleted. Attachment Deleted.

[2014-10-24 20:51:49] #4 [Go To First Post]
Sawtooth - Posts: 3992
It works for me. Do the Session Times span 03:45:00 ?

[2014-10-26 22:16:04] #5 [Go To First Post]
User48557 - Posts: 138
Yes it does. ill show my settings


coco study.PNG / V - Attached On 2014-10-26 22:06:39 UTC - Size: 190.99 KB - 334 views

[2014-10-26 23:39:59] #6 [Go To First Post]
Sawtooth - Posts: 3992
To see a weekly line on a 1 min chart, you'll need 10000 spreadsheet rows. 1440 x 7 = 10080

[2014-10-27 13:36:02] #7 [Go To First Post]
User48557 - Posts: 138
The chart settings say 1 minute but its being used for a profile chart, it works when I use it for other profile charts. I noticed its only the charts that open on Monday rather Sunday that are giving me trouble. The formula works for every market except, Coffee, Coco, sugar, because they all open on Monday ( it seems )


coco 11.PNG / V - Attached On 2014-10-27 13:35:46 UTC - Size: 199.13 KB - 344 views coco12.PNG / V - Attached On 2014-10-27 13:35:58 UTC - Size: 208.38 KB - 340 views

[2014-10-27 13:36:48] #8 [Go To First Post]
User48557 - Posts: 138
I fixed the row count and still nothing

[2014-10-27 13:48:25] #9 [Go To First Post]
Sawtooth - Posts: 3992
Your earlier pics show a gap in times in column A, from 2014-10-23 17: to 2014-10-24 07: The formula cannot find 03:45. There is no volume between those hours in the markets you are having trouble with. To fill the overnight gaps, go to Chart >> Chart Settings >> Advanced Settings tab >> check Include Columns With No Data. Date Time Of Last Edit: 2014-10-27 14:39:48

[2014-10-27 14:37:28] #10 [Go To First Post]
User48557 - Posts: 138
ok I don't really understand what im doing wrong, this is a example of sugar. Sugar opens 3:30 am central time. According to sierra chart


Attachment Deleted. sugerr.PNG / V - Attached On 2014-10-27 14:37:18 UTC - Size: 161.8 KB - 314 views

[2014-10-27 14:40:38] #11 [Go To First Post]
Sawtooth - Posts: 3992
I edited post #9. This will solve your problem.

[2014-10-27 14:51:05] #12 [Go To First Post]
User48557 - Posts: 138
I just tried that with sugar and I ended up with a neon vertical line after every profile chart, but still no Opening line


suga1.PNG / V - Attached On 2014-10-27 14:51:00 UTC - Size: 153.26 KB - 326 views

[2014-10-27 15:03:23] #13 [Go To First Post]
User48557 - Posts: 138
Coco did work with that new setting, just sugar I sill need to maybe play around with

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