Hello I am trying to write a formula where Cells A2:A16 are searched for numerical values and the results are output in cells C2:F2. Within the data that will populate in A2:A16, there will only be 0-4 occurrences of a numerical value only (as seen below in column A). For value 1 & 2 it will always be a number between 0-101 and for values 3 & 4 it will always be a number between 102-999. What is the best formula I can write for cells C2:F2 to get the results to populate like they do in yellow below? Any help much appreciated :)
1 Answer
Use filter()
, like this:
cell C2:
=transpose( filter(A2:A, isnumber(A2:A), A2:A <= 101) )
...and:
cellE2:
=transpose( filter(A2:A, A2:A > 101) )
The isnumber(A2:A)
bit is required to skip empty cells whose value is considered zero and thus below 101
.