Gaps in a series of numbers, with conditions

[bines53]

Hello friends,

I'm looking for a solution, how do I find the correct numbers.
For example, given 10 numbers in ascending order,
1
3
20
40
48
63
68
70
83
100

I have to find them numbers, if I will place them in the current series, the absolute gap will be not less than 7.

Correct numbers are,
10 ,11 ,12 ,13 ,27 ,28, 29 ,30, 31 ,32 ,33, 41 ,55 ,56 ,90 ,91 ,92 ,93.
For example, number 89, is not true, because the number of ninth in the series, 83, less than 89 gives 6 .

Thank you !

 

[bines53]

The file,

 

[NARAYANK991]

Hi David ,

Something like this ?

I have assumed that only one number can be inserted in between two numbers ; if the gap is bigger , then this logic will not work.

Narayan

 

[bines53]

Hi Narayan,

A good idea, you can do so with the function OFFSET ?

Thank you !

David

 

[NARAYANK991]

Hi David ,

Isn't OFFSET your most unfavoured function ?!

I think using OFFSET will be more cumbersome , since it does not allow the first parameter to be anything other than a reference.

Narayan

 

[NARAYANK991]

Hi ,

See the uploaded file.

Narayan

 

[bines53]

Hi Narayan,

OFFSET function,I use non-volatile version,Vision.xll,
https://tkljh.s3.amazonaws.com/quant/tools/excel.html.
XlOffset

I try to think in a different way,
If I set a number, for example, 13, the formula should give me an answer, true, and if I set a number, 14, the formula should give me an answer, false.
Can you give me such a formula?

Thank you !

David

 

[NARAYANK991]

Hi David ,

See if this is OK.

Narayan

 

[bines53]

Hi Narayan,

Amazing !!!

Very interesting use of the function AND !

Thank you friend !

David

 

[bines53]

Hi Narayan,

Another question, following the final solution, is there a way to find the total, who are conditioned, that is, the answer should be 17,


Thank you !

David

 

[NARAYANK991]

Hi David ,

If you make use of the helper outputs , a simple COUNTIF will return the wanted result , but without these outputs , it is more difficult. I am not sure I can help.

Narayan

 

[bines53]

Hi Narayan,

I made some changes according to your logic, if I can improve more, I'll update.


Thank you !

David

 

[bines53]

Hi Narayan,

The solution to find the total =17
Now I check, if you can get all the numbers, One formula, you see what inspiration you gave me?

David

 

[John Jairo V]

Hi @bines53 (and @NARAYANK991)

Maybe could be shorter, but this is my first approach. Blessings!

 

[bines53]

Hi @John Jairo V ,

Genius, we have already said !!!

Amazing !!!

Still did not go deep into the formula, it seems to me that you can shorten your beautiful formula.

Thank you !

David

 

[John Jairo V]

Hi again!

Second sight of the problem. Refined formulae a little. Blessings!

 

[bines53]

Hi @John Jairo V http://forum.chandoo.org/members/john-jairo-v.18897/

Amazing, how your control such a complex formula !


Thank you !

David

 

[bines53]

Hi @John Jairo V

I check if it is possible, with the function MOD, as a post 13.

Thank you !

David

 

[John Jairo V]

Hi again!

Another One (no CSE in column E):

=IF(ROWS(E$2:E2)>D$1,"",AGGREGATE(15,6,COLUMN(A$1:INDEX($1:$1,MAX(A:A)))/((COLUMN(A$1:INDEX($1:$1,MAX(A:A)))>=A$2:A$11+G$1)*(COLUMN(A$1:INDEX($1:$1,MAX(A:A)))<=A$3:A$12-G$1)),ROWS(E$2:E2)))

Blessings!

 

[bines53]

Hi @John Jairo V

Perfect !!!

I have no other words to say, you're a genius !

You became the logic is simple and easy !

Thank you !


David