Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sat, 01 Jun 2024 22:51:12 +0000
[Programming Help] - Help with a Spreadsheet Sum column upon a condition then start from 0
View Count: 647
| [2020-12-05 21:19:20] |
| User215984 - Posts: 24 |
|
Hi, I would appreciate any help on how to sum a column if: A. Close <= Previous Close Sum Col{x} starting at this row B. Until Close >= Previous Close Start from 0 and sum again (A) happens again Close Val Sum/Total 11.25 3 3 11.24 5 8 11.23 2 10 - 11.21 7 7 11.22 2 9 11.22 3 12 11.28 1 13 - 11.20 4 4 11.19 2 6 Thanks! |
| [2020-12-05 21:40:37] |
| Sawtooth - Posts: 4019 |
|
Try this, in cell Y3: =IF(E3>=E4,0,IF(E3<=E4,X3+Y4,Y4)) |
| [2020-12-05 21:56:40] |
| User215984 - Posts: 24 |
|
Thanks, but it didn't work see attached jpeg Column W is the boolean of the close. Column X is the data to be Sum'd Date Time Of Last Edit: 2020-12-05 21:58:01
|
 Spreadsheet.JPG / V - Attached On 2020-12-05 21:54:43 UTC - Size: 55.01 KB - 183 views |
| [2020-12-05 22:02:51] |
| Sawtooth - Posts: 4019 |
|
You need to use X3 instead of S3 in your formula.
|
| [2020-12-05 22:11:39] |
| User215984 - Posts: 24 |
|
Tomgilb, Sorry, I didn't update the header it is X3 see attached again. Same results. Thanks!!! |
Attachment Deleted. |
| [2020-12-05 22:30:00] |
| Sawtooth - Posts: 4019 |
|
The column Y formula sums the current X value with the previous Y value, and resets to zero when W is FALSE. What values are you expecting? Manually enter the expected values in column Z, leaving Z3 blank, and give me a screenshot of a W3:Z20. |
| [2020-12-05 23:19:37] |
| User215984 - Posts: 24 |
|
tomgilb, Thanks for your time!!!! I guess this is like a zig-zag volume type of calculation restarts to sum when a change in trend. I'm using col W as a boolean for the (close <= close[-1]) test. Col Z sums everything for col X, until col W changes state and then starts re-sum from the current row until another col W change state Date Time Of Last Edit: 2020-12-05 23:27:58
|
| [2020-12-06 02:36:45] |
| Sawtooth - Posts: 4019 |
|
The spreadsheet study's array is in descending order: the current price bar is in row 3, and earlier bars are in rows below. The Close prices are in column E, which is in descending order, so it's not possible to do what you want in a Formula Column. If you could reverse it to ascending order, it would have no relationship to the chart bars. The formula I offered does what you want, but in descending order. |
| [2020-12-06 16:35:48] |
| User215984 - Posts: 24 |
|
Thanks! I found this on the web for Excel and it does what I need. Could it be converted for the SC spreadsheet? =SUM($A$2:A2)-IFERROR(SUM(OFFSET($A$1,1,0,MATCH(LOOKUP(2,1/($C$2:C2>0),$C$2:C2),$C$2:C2,0)-1,1)),0) |
| [2020-12-06 18:56:37] |
| Sawtooth - Posts: 4019 |
|
Do you want the Y column in the opposite order as the chart bars?
|
| [2020-12-06 19:40:33] |
| User215984 - Posts: 24 |
|
No
|
| [2020-12-06 23:38:06] |
| Sawtooth - Posts: 4019 |
|
Your S-2.jpg screenshot has column Z in the opposite order of column E and column W. This is not possible to do in a Formula Column because you cannot change values in previous rows (bars) that have closed. In a spreadsheet study's sheet that is associated with a chart, you always need to work from the bottom up, not the top down as you do in Excel. Please explain why you need these changed values and maybe there is a workaround. |
| [2020-12-07 00:18:02] |
| User215984 - Posts: 24 |
|
Hi Tomgilb, Thanks for your time, I really appreciated it!!!! I just found what I needed with Zyp's software. |
To post a message in this thread, you need to log in with your Sierra Chart account: