Home >> Support Board

Support Board

Date/Time: Thu, 25 Apr 2024 23:37:27 +0000

How to Post a Message for Support

[Programming Help] - How to SUM spreadsheet column consisting of +Num's,-Num's, and 0's while...

View Count: 1584

[2018-09-12 21:21:14] #1 [Go To First Post]
User68474 - Posts: 195
How can I SUM a spreadsheet column consisting of +Num's,-Num's, and 0's, trying to SUM three different ways: 1.) Get a total by disregarding the negative signs; 2.)Total of positive only; 3.) Total of negative only. ALSO, I'd like to know how to get the total number of entries in any given column(i.e. "rows",I guess) after eliminating all the "0"s. From previous help on this Forum, I learned how to create the "IGNOREZEROS" formula. I realize I can probably "extrapolate" this by summing and then averaging and then dividing the sum by the average. But I figure there must be a more "sophisticated" way via a formula of some sort which will be helpful for creating other more complex formulae if desired. You out there, "tomglib"? Hope so.

[2018-09-13 00:20:04] #2 [Go To First Post]
Sawtooth - Posts: 3976
SUMIF and COUNTIF are not supported so you'll need to use 2 columns. This is as sophisticated as it gets. Spreadsheet Functions: Using the Equivalent of COUNTIF, AVERAGEIF, MAXIF, MINIF, SUMIF

[2018-09-13 01:45:19] #3 [Go To First Post]
Sawtooth - Posts: 3976
A SUMIF example, where P3 is the column with -, +, and zeros: Q3: =IF(P3>0,P3,0) R3: =SUM(Q3:Q1002) this will sum positive numbers S3: =IF(P3<0,P3,0) T3: =SUM(S3:S1002) this will sum negative numbers

[2018-09-13 04:52:16] #4 [Go To First Post]
User68474 - Posts: 195
Thanks, "tomglib". I had read that documentation previously. Problems arise immediately, however, when trying to implement the formulae. I'm working with a ZigZag study again, trying to derive those sums from Line Lengths (in Ticks on chart, but looks like it's in points in spreadsheet column. Doesn't make any difference for what I'm after), generically IDxx.SG4. Nothing working so far. Too tired to stick with it now. Will try again tomorrow. Thanks again.

[2018-09-13 21:45:22] #5 [Go To First Post]
Sawtooth - Posts: 3976
Yes, the ZZ Line Length is in points. Same formulas, using a specific ID# and SG#: Q3: =IF(ID2.SG4@3>0,ID2.SG4@3,0) this returns only the positive Line Lengths. R3: =SUM(Q3:Q1002)/$J$21 this will sum positive Line Lengths and convert to ticks. S3: =IF(ID2.SG4@3<0,ID2.SG4@3,0) this returns only the negative Line Lengths. T3: =SUM(S3:S1002)/$J$21 this will sum negative Line Lengths and convert to ticks. Date Time Of Last Edit: 2018-09-15 00:27:40

[2018-09-14 20:19:45] #6 [Go To First Post]
User68474 - Posts: 195
Returns nothing but "0" !!! Only operation I've been able to successfully accomplish(manually summing first 1000 of them up myself, bleeding my eyeballs to tediously extract the integers from all the zeroes!) is "=SUM(column letter3:column letter1000)" which, of course, is useless to me. At least it was accurate!!!

[2018-09-15 00:32:17] #7 [Go To First Post]
Sawtooth - Posts: 3976
The formulas return values for me. Some possible issues: 1. The formulas assume the ZZ study is ID2. Edit as needed to the ID# of your ZZ study. 2. The formulas assume the 'Number of Rows' is set to 1000. Confirm this in your settings. 3. Confirm you are working on the Sheet# that matches the 'Formula Source Sheet Number' setting. 4. The returned values don't seem to be useful. Exactly what are you trying to do?

[2018-09-15 01:31:34] #8 [Go To First Post]
User68474 - Posts: 195
Reconfirm #'s 1-3: Get a total this time...but the result for "...>0" and "...<0" are both the same...that should not be...mysteriously, however, that would net 0 )!! Date Time Of Last Edit: 2018-09-15 01:32:03

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