…understanding the ARRAY_LITERAL ERROR: until both queries/filters/formulas output something then all is good: however if one of those queries/filters/formulas doesn’t have anything to output it outputs #N/A – No matches are found in QUERY/FILTER evaluation. – the issue is that #N/A is only in the 1st cell: but array expects that matrix on both sides … Read more
=IF(DATEDIF(A1, B1, “D”)>365, QUOTIENT(DATEDIF(A1, B1, “D”), 365)&” year(s) “& QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30)&” month(s) “& MOD(QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30), 30)&” day(s)”, IF(DATEDIF(A1, B1, “D”)>30, QUOTIENT(DATEDIF(A1, B1, “D”), 30)&” month(s) “& MOD(DATEDIF(A1, B1, “D”), 30)&” day(s)”, DATEDIF(A1, B1, “D”)&” day(s)”))
try: =ARRAYFORMULA(INDEX(SPLIT(REGEXREPLACE(A8:A12, “https?://www.|https?://|www.”, ), “/”),,1)) UPDATE 1: =ARRAYFORMULA(IFNA(REGEXEXTRACT(INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1), “\.(.+\..+)”), INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1))) UPDATE 2: =INDEX(IFERROR(REGEXEXTRACT(A1:A, “^(?:https?:\/\/)?(?:ftp:\/\/)?(?:www\.)?([^\/]+)”)))
To achieve the result you’re looking for, you can use BYROW to supply the argument once per array: =BYROW({1;5},LAMBDA(row,INDEX(A2:B16,row,2))) BYROW sends the array argument provided once per row to the function inside LAMBDA. As a general formula, =BYROW(range, LAMBDA(row, your_formula(row))) If you want to send two arguments, use MAP instead. =MAP({1;5},{1;2},LAMBDA(arr_1,arr_2,INDEX(A2:B16,arr_1,arr_2))) This will get row … Read more
Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
AND doesn’t work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions. I.e. it checks if “”>”” which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down. You can use multiplication of … Read more
Assuming no more than 100 rows try this “array formula” to count the different names in A2:A100 where there is a 1 in the same row in B2:B100: =SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>””,MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1)) confirmed with CTRL+SHIFT+ENTER Note that I say different not unique as the two are not the same
QUERY level 1: if all 5 cells in range C2:G have values: =QUERY(QUERY(C2:G, “select (C+D+E+F+G)/5”), “offset 1”, ) if not, then rows are skipped: if empty cells are considered as zeros: =INDEX(QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )) to remove zero values we use IFERROR(1/(1/…)) wrapping: =INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )))) to make Col references … Read more
Issue: Undesirable addition of empty strings in all the available rows by traditional usage of ARRAYFORMULA(IF(A:A=””,…)) Solution: Using ARRAYFORMULA properly with INDEX/COUNTA(to determine the last row that’s needed) ensures formula is only filled upto the needed row instead of a camouflage INDEX/COUNTA: INDEX returns a value as well as a cell reference. A2:INDEX(A2:A,COUNTA(A2:A)) => If … Read more
internal custom number formatting solution: sadly, the internal formatting in google sheets is by default able to work with only 3 types of numbers: positive (1, 2, 5, 10, …) negative (-3, -9, -7, …) zero (0) this can be tweaked to show custom formatting like thousands K, millions M and regular small numbers: [>999999]0.0,,”M”;[>999]0.0,”K”;0 … Read more