Support Board
Date/Time: Mon, 20 Jan 2025 11:13:55 +0000
[Programming Help] - Spreadsheet formula for Nth non zero value in column
View Count: 1389
| [2018-06-07 17:47:23] |
| aknsyu71@gmail - Posts: 54 |
|
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: 4157 |
|
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: 54 |
|
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: 4157 |
|
Then in column D use =C1<>0
|
To post a message in this thread, you need to log in with your Sierra Chart account: