player0
player0

Reputation: 1

how to generate mirrored sequence of numbers

I'm looking for a way (lightweight one-cell formula solution) how to generate a mirrored sequencing based on the count of unique values. example for 2, 3 and 4 given values:

enter image description here

Upvotes: 2

Views: 369

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

Please try the following

={TRANSPOSE(SEQUENCE(ROW(A15)-ROW(A12)+1)), 
  TRANSPOSE(SORT(SEQUENCE(ROW(A15)-ROW(A12)+1),1,0))} 

enter image description here


Later addition
As commented by player0, one can also use

={TRANSPOSE(SEQUENCE(COUNTUNIQUE(A12:A15)+1)), 
      TRANSPOSE(SORT(SEQUENCE(COUNTUNIQUE(A12:A15)+1),1,0))}  

The difference being that using the first formula one can create the sequence even when referencing just empty cells.

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34325

I set myself the challenge of doing this in a single function - it must be possible, right? You just need a symmetrical function.

After some thought I came up with a function of the form:

y=c-abs(x)

So if you can generate x-values like (for the case of n=3)

-2.5 -1.5 -.5 0.5 1.5 2.5

you just need to subtract their absolute values from 3.5 to get

1 2 3 3 2 1

Unfortunately Sequence will only generate integer values so the formula isn't as tidy as one would wish:

=ArrayFormula(counta(A:A)+0.5-abs(sequence(1,counta(A:A)*2,-counta(A:A))+0.5))

enter image description here

Upvotes: 1

Related Questions