Make a list of incremented numbers from a set of numbers-CodePudding

I've been trying to write a formula to create a list from a given set of numbers. I tried to do this with "if" function but failed many times.

The rule is to create a set of numbers by adding -2, -1 and 1, 2 to each number so that growing the size of the original list exponentially.

so if the original list includes is (x,y), the calculated new list would include (x-2,x-1,x,x 1,x 2, y-2,y-1, y, y 1, y 2).

Original numbers are all positive, and the calculated list is expected to be all positive, and should not include zero and repetitive numbers neither.

E.g,

in columns A1,B1,C1,D1, etc

List = 1, 2, 4, 5, 9, 10, 22, 25 (in this case there is a total of 8 numbers)

when you add -2, -1 and 1, 2 to each number we get ;

    Calculated values =   -1, 0, 1, 2, 3,  0, 1, 2, 3, 4,  2,3,4,5,6,
 3,4,5,6,7, 7,8,9,10,11, 8,9,10,11,12, 20,21,22,23,24, 23,24,25,26,27 
    
    Final Calculated List should be : 1,2,3,4,5,6,7,8,9,10,11,12,20,21,22,23,24,25,26,27

( all repetitive and negative numbers, 0's are omitted)

I realized that I can't do this with if function, can I get help how to solve this problem? please. thanks in advance.

CodePudding user response：

try:

=TEXTJOIN(", ", 1, INDEX(QUERY(SORT(UNIQUE(FLATTEN(
 SPLIT(A1, ",") {-2; -1; 0; 1; 2}))), "where Col1>0", )))