Home >> Support Board

Support Board

Date/Time: Fri, 26 Apr 2024 07:43:36 +0000

How to Post a Message for Support

[User Discussion] - Need help with " Spreadsheet study "

View Count: 1503

[2014-10-07 18:53:08] #1 [Go To First Post]
User48557 - Posts: 138
=IF(AND(WEEKDAY(A3)=2,A4-INT(A4)<TIMEVALUE("08:30:00"),A3-INT(A3)>=TIMEVALUE("08:30:00")),B3,K4) Is a formula that starts a line at 8:30 am Monday and goes till next Monday at 8:30 am then starts another line and so on. What would the formula be or how would the formula differ if I wanted to start a line on Sunday at 5 pm to do the same? I have little to no spreadsheet experience so if you can write it out as the other formula is.

[2014-10-07 19:22:41] #2 [Go To First Post]
Sawtooth - Posts: 3976
Refer to the WEEKDAY function documentation: http://office.microsoft.com/en-us/excel-help/weekday-HP005209336.aspx?CTT=5&origin=HP005204211 And use the 24 hr times in the TIMEVALUE function. =IF(AND(WEEKDAY(A3)=1,A4-INT(A4)<TIMEVALUE("17:00:00"),A3-INT(A3)>=TIMEVALUE("17:00:00")),B3,K4)

[2014-10-07 19:25:59] #3 [Go To First Post]
User48557 - Posts: 138
Ok great I appreciate the help

[2014-10-07 22:19:31] #4 [Go To First Post]
User48557 - Posts: 138
I am still having trouble with the formula. I am putting that formula in cell block k3 and nothing is changing or happening to the chart. What can be the reason why ?

[2014-10-07 23:44:56] #5 [Go To First Post]
Sawtooth - Posts: 3976
You probably need to recalculate the chart. Go to Chart >> Recalculate.

[2014-10-08 00:58:44] #6 [Go To First Post]
User48557 - Posts: 138
I just tried to recalculate and nothing is happening, the weird part is =IF(AND(WEEKDAY(A3)=2,A4-INT(A4)<TIMEVALUE("08:30:00"),A3-INT(A3)>=TIMEVALUE("08:30:00")),B3,K4) worked for me the first time I was punching it in, then I tried the new formula given for A Sunday Line and that didn't work at all, now that I'm going back and punching the old one in , neither of them work now. I have been trying to use different markets and charts and I'm getting nothing as far as any lines being drawn.

[2014-10-08 03:46:36] #7 [Go To First Post]
Sawtooth - Posts: 3976
Check these things: -The formula goes in row 3 of one of the Formula Columns. -The relative column's Subgraph must be set to something other than Ignore or Hidden, preferably Dash. -The Chart Region must be set to 1. -Hide Study must be unchecked. -The chart will need recalculation when the formula is edited/added. -The formula should go on the Sheet# (tab position) corresponding to the Chart number. -Formula Source Sheet Number should match the Sheet# (tab position) and correspond to the Chart number. -Chart Data Output Sheet Number should be set to 0.

[2014-10-08 13:42:34] #8 [Go To First Post]
User48557 - Posts: 138
Yup it all checks out, Still nothing. I screenshot some images so you can see all my settings. I noticed that after I paste the code in cell k3, I minimize the spreadsheet screen after and then go recalculate the chart. Once I open the spreadsheet up again the code is gone out of k3.


Capture spreadsheetstudy.PNG / V - Attached On 2014-10-08 13:32:34 UTC - Size: 157.86 KB - 361 views Capture 2.PNG / V - Attached On 2014-10-08 13:32:48 UTC - Size: 167.77 KB - 364 views Attachment Deleted.

[2014-10-08 13:58:12] #9 [Go To First Post]
Sawtooth - Posts: 3976
The Formula Source Sheet Number is set to 4, and the spreadsheet cell A1 shows you are on Chart# 2. Set the Formula Source Sheet Number to the same as the Chart number, and put the formula on the Sheet# (tab position) that matches the chart number.

[2014-10-08 14:55:07] #10 [Go To First Post]
User48557 - Posts: 138
That sure was the problem. It working just fine now

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