5

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =SEQUENCE(A2,1,A4,A6) SEQUENCE


3

Instead of date 'TODAY()' use date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'" NOTES: The last " should be placed in a proper place in order to have a text value as the second argument of QUERY. 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis. Try this =ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, "...


3

With a regex, you can replace each character with itself + ','. Then use a regular split function. =split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)


3

This formula, Dragged down in column B: =ARRAYFORMULA(IF(A2="",,QUERY(QUERY(IMPORTRANGE(A2,"Sheet1!B2:F10"), "select * where Col1 is not null" )&"|",,9^99))) And then this formula just one time in cell H2: =ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(TRANSPOSE(QUERY(B2:F,,9^99)),"|")))) Should allow you to build the aggregated dataset you're looking for.


2

EDIT : The easiest way to generate an array of sequential number in Google Sheets is the =Sequence function. Syntax : SEQUENCE(rows, columns, start, step) SEQUENCE function on Google's Help Center =Sequence(A2) There's a way without dragging a cell with Arrayformula : =ArrayFormula(ROW(INDIRECT("A1:A"&A2))) Where A2 is the n value There'...


2

Use VLOOKUP(). Assuming your lookup table is in H1:I50, you'd put the following in B1: =VLOOKUP(A1,H1:I150,2,FALSE)


2

There are multiple ways to do this : Query, Filter but I think SUMIF will suits the most : =SUMIF(arrayformula(regexmatch(A1:A10;C1));true;B1:B10) =SUM(FILTER(B1:B10;arrayformula(regexmatch(A1:A10;C1)))) =trim(REGEXREPLACE(CONCATENATE(QUERY(A1:B10;"SELECT sum(B) where A = '"&C1&"'"));"sum";)) Spreadsheet : A | B | C ---------------...


2

It looks like you are looking for a single formula solution but they could be hard to understand and maintain, specially if you aren't familiar on how the Google Sheets related features works. Related features Dates. Google Sheets handles dates as serialized numbers, where a day is one unit. Because of the above feature we could create a list of dates by ...


2

If I remember my highschool days, your expression reads : Sum of the n from 1 to 30. I.E. 1000*1.07^1 + 1000*1.07^2 + 1000*1.07^3 + ... Here's a way to do it : =sum(ArrayFormula(1000*power(1.07,(ROW(A1:A30))))) Result is 101 073.04


2

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\S+)"),""))),"\s+"," ") You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...


2

You can also use this single robust formula in cell B2: =IFERROR(ArrayFormula(if(LEN(A2:A),TRIM(SPLIT( REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","$1"&" ")," ") ),"")),"") An even simpler formula can be used if you don't have any empty rows in ...


2

This one is fairly strait forward, please see the code below: =textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4))) You can change the last 4 to a 1 if you want absolute references.


2

EDIT (following OP's comment) To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function. =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(FILTER(A44:H44,A44:H44<>"")),"L",""))*1)) Initial answer You can use this simple formula: =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(A44:H44),"L",""...


2

You can try =sumif() =SUMIF($B$1:$E$1,"B",B2:E2) Here's a dummy sheet You just need to replace B with Winning/Losses or whatever you need.


2

How can I make a COUNTIF(or similar) that counts the amount of people join within a time period? You do not really need any regex function. You can use the different versions of the following formula instead. To find all values within the second month (February) =ARRAYFORMULA(COUNTIFS(V21:V38,"<>",MONTH(U21:U38), "="&2)) To just find the Y ...


2

Edited answer (following OP's comment) Your formula works a treat but I failed to say that the column of numbers will contain empty cells - is it possible to tweak your formula so that it ignores empty cells? =INDEX(D:D,LARGE(IF(D:D<>"",ROW(D:D)),2)) The content of the second to last row with value in range D:D is provided by the above formula. ...


2

EDIT (following OP's edited question) Please use the following in cell D2: =ArrayFormula(IFERROR(VLOOKUP(E2:E,$A$2:$C,3,0))) Original answer Please use the following formula: =ArrayFormula(IF(A2:A13=E2:E13,C2:C13,"")) (of course you can adjust the ranges to your liking) If on the other hand need to show the results as in your example, use this one:...


2

You can do that using the following arrayformula in cell E2: =ArrayFormula(INT((ROW(A2:A)-1)/9)) How the formula works: First of all you need to make sure you place it in the correct row. That is if your range starts at the second row (like E2-E) row you place it in the second row. As you notice in the given formula the column is A. That is to show you ...


2

Edit In your Google thread you mention: Would really like to be able to convert this in one formula/step. ... I would love if it if could handle the whole column down. In this case, you can use the following formula =ArrayFormula(IFERROR( (REGEXREPLACE(REGEXEXTRACT(A2:A,"(^\d+\.\d+\.\d+)"),"\.","\/") &" " &REGEXREPLACE(...


Only top voted, non community-wiki answers of a minimum length are eligible