New answers tagged google-sheets-arrayformula
0
I've realised there's a better way to determine if a time is before or after midnight - just test the time component using HOUR. If the hour is between 0 and 6, the time can be considered to be after midnight. Otherwise it's before.
=AND(HOUR(C2)>=0, HOUR(C2)<=6)
0
The following method works, and is not too arcane:
A: I defined 3 named ranges, just to make my formulas easier. This sheet is called 'Fix' so the named ranges use Fix as a prefix.
FixDataToChange is the stuff with the errors in it.
FixRegex (Yellow) are the patterns to use. Most of them are simple text patterns.
FixChangeTo is the material to replace ...
1
If you're trying to SUM() a column based on criteria, then SUMIF() or SUMIFS() would get you what you want. In this example you could use SUMIFS()
=SUMIFS(P:P, A:A, "PG", F, "Indexed") - this gives you a SUM of values in P where Column A = "PG" and Column F = "Indexed". You could extend these criteria for as many filters as you need.
You'd then change this ...
1
You can use the following formula in cell A4.
=ArrayFormula(IF(
ARRAYFORMULA(SUMIF(IF(COLUMN(E4:J4),ROW(E4:J8)),ROW(E4:J8),E4:J8)<132)*
ARRAYFORMULA(SUMIF(IF(COLUMN(E4:K4),ROW(E4:K8)),ROW(E4:K8),E4:K8)>=132),1,"Oh no!"))
(The above formula assumes data is in E4:K8. Please adjust it to fit your data.)
The following formula can be used as an ...
1
As you're only summing a few columns, you could do something like this:
=SUMPRODUCT(ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150)<132),ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150+K4:K150)>=132))
Using ARRAYFORMULA with functions that already take a range gets overly complicated. I think this solves it for you fairly ...
0
If I understood your question correctly the following formula should produce your desired results:
=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(arrayformula(if(iferror(
VLOOKUP(SPLIT($K3:K, ",", TRUE, TRUE),
QUERY(Tags!A2:C28,"SELECT A,'z' WHERE C=1 label 'z' ''"), 1, 0),
FALSE)<>FALSE, "YES","xxxxx")),LEN(A3:A)>1)),,999^...
0
You can't pass a range to ISBLANK just as you did isblank(B:B) because if you want to pass a range, it actually does need the same ARRAYFORMULA function.
If you want to make it work, you can do it this way:
=ARRAYFORMULA(IF(ISBLANK(B4:B),"",IF(B4:B>=900,B4:B*0.2,IF(B4:B>=400,B4:B*0.15,IF(B4:B>=200,B4:B*0.1,IF(B4:B<=199,B4:B*0,FALSE))))))
Check ...
Top 50 recent answers are included
