New answers tagged

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