New answers tagged

0

You can have the same functionality in sheets as well. &T(N("comment")) OR =IFERROR(A1/A2,"") &T(N("comment")) OR =YourFormula+N("comment") (This last one works when the formula gives numeric results)


0

Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy. Supose you have all in A1:C5 and E1:G5 data in one sheet. All you need is only put a line like that: =LEFTJOIN("A1:C5";"E1:G5"; "A=F";0) We can see a working example here I used code from ...


0

If I understand this correctly a simple if function should work. Try this: =arrayformula(IF(A18=F2:F,H2:H,""))


0

Since you want to return the sum of each of 3 columns you could use the following query formula instead =QUERY(QUERY(A:D, "select sum(A), sum(B), sum(C) where month(D)+1=2 ",1), "select * OFFSET 1 ",0) The above formula will give you results for just the month of February. You can alter the formula to get results for each month ...


0

This will return TRUE or FALSE: =IFERROR(SEARCH("keyword",A1)>0, FALSE)


0

Here's a way to pick out duplicate at a domain level. =filter( A2:A6, ArrayFormula( REGEXMATCH( A2:A6, join("|",unique(ArrayFormula(REGEXEXTRACT(B2:B11,"@(.*)")))) ) ) ) First I used regextract to extract the domain of Col Band join them ...


0

First you need to show formulas : Then copy and paste values only.


0

One cell solution: Combining the FILTER Function with the COUNTIF function provides you with the list you want: =FILTER(A:C,COUNTIF(D:D,A:A)=0)) In this example I used the Key as the matching field comparing column A with matches in D but it works with the other columns as well.


0

It just combines these formulas, so if you need to add some more data - have to do it manualy ={ TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D2&",", C2))), ",")), ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, 1, 0, 1), 1)); TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D3&",", C3))), ",")), ARRAYFORMULA(DATE(YEAR(A3), MONTH(A3) + SEQUENCE(C3, 1, ...


0

You are tracking behaviours per time period. The behaviours are: "Disruption" and "Task Refusal". You want to calculate the average of the last 14 days. The layout of your spreadsheet is a barrier to calculation of the 14 day average. Dates are recorded on the horizontal axis (rather than vertical), and each date is merged across two columns - each column ...


Top 50 recent answers are included