Find values of an array based on an interval

Question

I would like to find values in an array that are at least an interval length apart. Lets say I have the following data set

 0.1 0.21 0.26 0.3 0.36 0.4 0.52 0.76 0.91 1.2

and my interval is 0.25. I need a formula that extracts the each time the data exceeds the interval. The interval starts from the last value that exceeded the previous interval. So the desired output I would like is the following:

0.1 0.36 0.76 1.2

The difficulty is in the 'interval' that is not static. I've tried things with arrayformula and sequence functions but not getting the desired results. Anyone have any pointers for me?

z.. · Accepted Answer · 2024-12-29 18:53:08Z

4

Here's a possible solution. Assuming your values are in A2:A11: *

=UNIQUE(SCAN(A2,A2:A11,LAMBDA(a,b,IF(b-a>0.25,b,a))))

^{* Credit to Chris}

edited Dec 29, 2024 at 18:53

answered Dec 29, 2024 at 18:16

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheMaster · Accepted Answer · 2025-01-02 13:00:38Z

1

An alternative, but a bit more verbose (with formula in B2):

=reduce(A2,A2:A11,lambda(a,c,if(c>chooserows(a,rows(a))+0.25,vstack(a,c),a)))

	A	B
1	Data	Result
2	0.1	0.1
3	0.21	0.36
4	0.26	0.76
5	0.3	1.2
6	0.36
7	0.4
8	0.52
9	0.76
10	0.91
11	1.2

or slightly shorter:

=reduce(A2,A2:A11,lambda(a,c,if(c>chooserows(a,-1)+0.25,vstack(a,c),a)))

or (remembering this is Google Sheets, not Excel)

=reduce(A2,A2:A11,lambda(a,c,if(c>chooserows(a,-1)+0.25,{a;c},a)))

edited Jan 2 at 13:00

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

answered Dec 29, 2024 at 20:14

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

3 Comments

TheMaster Dec 31, 2024 at 6:09

Hi @Tom Please do not upload images of code/data/errors.

Tom Sharpe Jan 2 at 10:00

Sorry for slow reply - you are right, but is there a way of showing the row numbers and column letters in markdown? I think I have seen it done, but don't know how.

TheMaster Jan 2 at 13:03

Thank you for removing the images and changing to markdown. There's no direct way to do this, except by adding an extra column with row numbers and extra top row with column letters.... My app (stackoverflow.com/a/79209836) may come in handy..( maybe I can change the code to automatically add these row numbers and headers later, if helpful)

Collectives™ on Stack Overflow

Find values of an array based on an interval

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related