Google-sheets – Google Spreadsheets map cell to array

google sheets

I'm trying to save space on Google Sheets and what I need is something like this:

Say I have a value in A1, call it X. I need an array of length N that contains N copies of X.

 {X,X,X,X,X,X,X...} N times

I want to do this without filling my spreadsheet.

Alternatively, what I really want is a means of running filter on a single condition instead of a range of conditions.

So filter(A1:A5,0<1) should return A1:A5 because 0 is always less than 1. Currently, filter only prints A1 because 0<1 evaluates as {True} and not {True, True, True, True, True}

Best Answer

Short answer

what I really want is a means of running filter on a single condition instead of a range of conditions.

=FILTER(A1:A5,ROW(A1:A5))

Explanation

Nesting ROW with a range as argument, in FILTER as well on ARRAYFORMULA and other similar functions, returns an array of integers greater than 0 which each of them are coerced as TRUE, so the result is {TRUE;TRUE;TRUE;TRUE;TRUE}

NOTE: Google Sheets use semicolons as row separators. See Using arrays in Google Sheets

Related Solutions

Google Sheets – Array-Safe Clamp Value

You can achieve the effect of CLAMP(value, min, max) with

=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)

which is compatible with ARRAYFORMULA and uses value only once. Explanation:

SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.
IFERROR returns the first argument, unless an error occurred in it, which case it returns the second
Thus, IFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min.
The other steps do a similar thing with max.

The above formula can be simplified, reducing the references to max and min from three to two of each:

=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))

Google-sheets – How to check if a cell contains any of an array of values

Short answer

Instead of SEARCH use REGEXMATCH and JOIN.

Explanation

SEARCH is for searching partial strings into other strings and if it will be used with arrays, both parameters should be arrays of the same size.

JOIN could be used to create a regular expression to be used by REGEXMATCH which in turn will return a array of TRUE/FALSE if the first parameter is an array and it is nested inside a ARRAYFORMULA.

Considering the OP examples, the formula to be used is

=ArrayFormula(REGEXMATCH(E1:E4, JOIN("|",{"Tim","Frank"})))

Best Answer

Short answer

Explanation

Related Solutions

Google Sheets – Array-Safe Clamp Value

Google-sheets – How to check if a cell contains any of an array of values

Short answer

Explanation

Related Topic