Support Board
Date/Time: Mon, 10 Mar 2025 07:07:01 +0000
[Programming Help] - Spreadsheet, nth most recent nonzero value?
View Count: 1789
| [2022-03-17 00:45:25] |
| j4ytr4der_ - Posts: 946 |
|
I have a study which returns nonzero numbers roughly every 5-10 bars. I would like to know what the 10 most recent nonzero values were. We have EARLIESTNONZEROVALUE and MOSTRECENTNONZEROVALUE which are great, but neither is quite what I need )other than getting just the first value present). I know of ways to do this in Excel, but it requires formulas SC doesn't support. Any of you clever folks have any ideas on how to get more than just the first or last nonzero values in a column? |
| [2022-03-17 01:27:27] |
| j4ytr4der_ - Posts: 946 |
|
I've got a solution that would work, except that the SMALL() function doesn't appear to work the way it is described. From the documentation: Returns the Nth smallest number from an array of numbers. The first argument is the array, while the last argument controls which number will be returned. 1 means the smallest number, 2 the second smallest and so on. Empty cells are not counted, TRUE will be considered as 1, FALSE as 0. Returns #VALUE! if the last argument is smaller than 1 or greater than the number of numerical values (and booleans) in the array.
But what it actually returns is the value contained in the Nth lowest row number, not the lowest value in the range. Support, is this how the SMALL() function is meant to work? If so that's unfortunate, and I think the documentation could use some tweaking to accurately reflect what it returns. Date Time Of Last Edit: 2022-03-17 01:28:08
|
| [2022-03-17 13:57:50] |
| Sawtooth - Posts: 4181 |
|
I would like to know what the 10 most recent nonzero values were.
You could use this example:Spreadsheet Example Formulas and Usage: Return Last Two Zig Zag Reversal Values It would take 10 Formula Columns. Date Time Of Last Edit: 2022-03-17 15:01:15
|
| [2022-03-17 15:12:04] |
| j4ytr4der_ - Posts: 946 |
|
Aha... interesting, thanks for pointing me to the example! I would still like to know if the SMALL() function is behaving as intended or not. Either the function isn't working right, or the documentation is wrong. But it definitely doesn't work the way it does in Excel, for example. |
| [2022-03-17 15:16:45] |
| j4ytr4der_ - Posts: 946 |
|
Tom, one small issue with this approach is that it's quite possible for me to have the same level separated by some cells of zeros, in which case the value repeats. I would prefer to only have any given value of the 10, be acknowledged once. Any idea of an efficient way to accomplish this? For example... 4340.75 4345.00 4341.75 4346.75 4346.50 4348.50 4349.75 4352.75 4352.75 4358.50 |
| [2022-03-17 18:23:26] |
| Sawtooth - Posts: 4181 |
|
It seems the SMALL function does not work if there are any zeros in the array; it returns 0 regardless of the nth smallest value. By contrast, the LARGE function works with zeros in the array, as expected. Are you trying to return the smallest of the 10 most recent unique nonzero values? |
| [2022-03-17 18:55:26] |
| j4ytr4der_ - Posts: 946 |
|
I'm just trying to return the most recent nonzero, then the next, the next, etc. But to do so, I have first done this to get the rows containing nonzero values... ROW(VLOOKUP(MOSTRECENTNONZEROVALUE(BS4:BS50), BS4:BS50, 1, TRUE))
Column BS of course contains the actual values I'm looking for. so from there, I just want the smallest number first, and so on so that I can build an INDIRECT() to get me the actual value. Hope that makes sense. Stupidly complex but, only way I can think to do what I want, and there is no UNIQUE() function in SC. Date Time Of Last Edit: 2022-03-17 18:55:43
|
| [2022-03-17 21:01:32] |
| j4ytr4der_ - Posts: 946 |
|
Oh and I neglected to confirm... yes ideally it would only be *unique* values.
|
| [2022-03-17 23:07:50] |
| Sawtooth - Posts: 4181 |
|
The SMALL function works if you replace the zeros with NOVALUE, in another column: =IF(BS3=0,NOVALUE,BS3) However, it doesn't skip duplicates either. The only way to simultaneously return the 10 most recent unique nonzero values to row 3 is to use at least 10 Formula Columns. Since you are using column BS, can I assume you want the 10 values in a one-column table instead (not associated with chart bars)? Date Time Of Last Edit: 2022-03-17 23:13:52
|
| [2022-03-17 23:17:03] |
| j4ytr4der_ - Posts: 946 |
|
I'd like them to be in one column, but honestly I can use 10 if need be, I was going down that route before this thread anyway. I'm going to try your SMALL() tip now and see where that gets me. Thanks!
|
| [2022-03-17 23:35:59] |
| j4ytr4der_ - Posts: 946 |
|
Aha hold up... I think I've got it. Not sure what was wrong before but I've got SMALL() doing what I needed now. Let me see if this gets me all the way home.
|
| [2022-03-17 23:42:40] |
| j4ytr4der_ - Posts: 946 |
|
Yep, got it now. Was a combination of the issue around SMALL() not handling zeroes, and also absolute vs. relative references in my range reference. Got what I wanted now. It takes 11 columns total but I can live with that. Not sure I'm even ultimately going to keep this idea but wanted to try it out. I appreciate the assist on this!!
|
To post a message in this thread, you need to log in with your Sierra Chart account: