Home >> Support Board

Support Board

Date/Time: Fri, 19 Apr 2024 01:49:33 +0000

How to Post a Message for Support

[Programming Help] - Cummulative Sum of a cell in spreadsheet

View Count: 291

[2023-01-27 14:18:29] #1 [Go To First Post]
af_99 - Posts: 22
Hi, I'm working with spreadsheets and would like to know if there is any way to make a cummulative sum of an individual cell (let's say AA3) that keeps changing every second. I mean: Second 1 : Cell(AA3) = 5 Second 2 : Cell(AA3) = 3 Second 3 : Cell(AA3) = 1 Second 4 : Cell(AA3) = 2 My desired value in another Cell(AB3): Second 1 : Cell(AB3) = 5 Second 2 : Cell(AB3) = 8 Second 3 : Cell(AB3) = 9 Second 4 : Cell(AB3) = 11 Is that possible in a spreadsheet? Thank you very much. Regards.

[2023-01-30 20:27:11] #2 [Go To First Post]
User183724 - Posts: 183
probably need a ACSIL study to do this

[2023-01-30 21:23:45] #3 [Go To First Post]
Sawtooth - Posts: 3973
Is that possible in a spreadsheet? In a word, no. It's not possible to capture an intrabar value in a spreadsheet study. This would require using ACSIL. However, you might be able to create a workaround using a 1 second chart. Date Time Of Last Edit: 2023-01-30 21:29:06

[2023-01-31 20:00:53] #4 [Go To First Post]
af_99 - Posts: 22
However, you might be able to create a workaround using a 1 second chart. Thanks tomglib. That is something that I considered, but then I came with another question: -Supposing my timeframe is 3min, so my main spreadsheet is in 3min and my auto trading system is made in that spreadsheet… can I use a second spreadsheet of 1second timeframe to do that calculation and then reference that calculation in the main spreadsheet? Is that the way to do it or there is another simpler way to do it? Thanks in advance.

[2023-01-31 21:16:12] #5 [Go To First Post]
Sawtooth - Posts: 3973
If you only need the most current sum, and don't need any historical sums, you could add a 1 second chart to the chartbook, with the same-named spreadsheet study. This will show as its own Sheet#, and you can reference its row 3. Use a formula like this on the 1 second sheet: =SUM(AA3:OFFSET(AA3,3,0)) This will sum 4 rows, rows 3 to 6, inclusive. (The OFFSET function starts its count in the row below, hence 3 instead of 4.) Since there are 180 seconds in 3 min, the rows won't align row to row between sheets, so referencing a row other than row 3 will give you erroneous values. SC recommends using the Study/Price Overlay study instead of a direct reference between sheets. But since you are only using the latest current sum, using the overlay study will likely not provide an advantage. If you want to sum all 180 1-sec bars, you would have some alignment between sheets, and using the overlay study would be recommended. So the question is: Can you get the same values on the 1 sec chart that appear intrabar on the 3 min chart. This is where a workaround like this might be problematic, or not work at all in a way that is useful.

[2023-02-11 12:01:32] #6 [Go To First Post]
af_99 - Posts: 22
Thank you very much tomglib. An amazing solution that helped me so much ;)

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