Login Page - Create Account

Support Board


Date/Time: Fri, 29 Mar 2024 08:05:16 +0000



[Programming Help] - Spreadsheet formula for Nth non zero value in column

View Count: 1244

[2018-06-07 17:47:23]
aknsyu71@gmail - Posts: 48
Hi, I cannot get this to work, hope someone will have the answer please.

Data in column is:
0
0
56
0
0
0
89
0
0

I am trying to reference the 89 value (2nd non zero value).
This works in excell with data in column C: =INDEX(C1:C10,SMALL(IF(C1:C10<>0,ROW(C1:C10)),2))

Would anyone know the correct formula for Sierra spreadsheets please.
Thank you
[2018-06-07 18:44:53]
Sawtooth - Posts: 3952
Try this:

Use a spare column to find non-zero values, e.g., column D:
=C1>0
and copy down an incrementing formulas to rows below, e.g. =C2>0 =C3>0 etc.

Then use this to return the second non-zero value in column C:
=INDEX(C1:C10,NMATCH(2,1,D1:D10,0),1)

Here's the documentation on the NMATCH function:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#NMATCH_Function
[2018-06-07 19:38:15]
aknsyu71@gmail - Posts: 48
Thank you Tom,
I really appreciate your help here again.
That solution almost worked but the column sometimes contains negative values hence I should have phrased the question: to return the 2nd non-zero value.

Thank you,
Andrej
[2018-06-07 20:04:29]
Sawtooth - Posts: 3952
Then in column D use =C1<>0

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

Login

Login Page - Create Account