Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
3

A1: =ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD"))) Drag to right. Two dates are initialized and a column number*7 is added per each column. TEXT to format dates " m/dd" JOIN the 2 dates by -


2

This does seem very identical to an older question/answer. In fact, so close that you can get the two by just taking my previous solution and breaking it apart. =ARRAY_CONSTRAIN( SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1),1,1) to find the value. =MATCH(B8,A1:1,0) to find its column "number" where A = 1 I have entered the formula in ...


1

Try ROUNDUP Rounds a number to a certain number of decimal places, always rounding up to the next valid increment.


1

I had a situation to randomly pick M or F in a column. I used this formula in Google Sheets: =IF(RANDBETWEEN(1,2) = 1,"M","F") Crude way, certainly not for long lists, but it works if your set of values is small.


1

Next to the keys on your second spreadsheet, add the following formula (assumes keys are on column A and amount on B) =SUMIFS(Sheet1!B:B,Sheet1!A:A,"="&Sheet2!A1) Update: to calculate the sum, of all matches, use the following (assumes your labels and values are on column A and B of Sheet1, and labels to sum are on column A of Sheet2) =sum(...


1

You probably want something like the below. In the brackets, first part is the logical expression 2nd is what to return if true and last is what to return if false. If it is not "ABC" then is will test to see if it is "DEF". If both are false it will return 0. =if(AA7="ABC",123,IF(AA7="DEF",456,0)) Let me know if this needs clarifying.


1

Try this: In your linked sample sheet, delete all of column D to prepare for the array formula. Next, with the entire Column D selected, set the format: Format > Number > Time Then place this formula in D1: =ArrayFormula(IF(ROW(A:A)=1,"Shift start time",IF(A:A="","",IF(C:C="Clock-in","(start)",VLOOKUP(B:B&"Clock-in"&A:A-TIME(0,0,1),SORT({B:B&...


1

OK, John, I've looked at your sheet. It gets a little complicated due to the fact that you're using named ranges. But here is what I placed in your sheet (a single formula in A1): =ArrayFormula({QUERY({"Number";ROW(INDIRECT("A1:A"&COUNTA(QUERY(DataTable,"Select 'Last_Name'"))-2))}),QUERY(DataTable,"Select * Order by 'Last_Name'")}) You'll recognize ...


1

OK, here is the formula I supplied: =IFS(B3="aaa","aaa",B3="nothing","",B3="myformula",ROUNDUP((1+2)/2),B3="myformula2",SUM(1+2)) It should be pretty easy to understand (i.e., condition, result, condition, result, etc.).


1

When using array formulas, they only know to which range they should apply calculations if you include that information. It can be confusing, since conditional formatting formulas will apply an array given just the top cell in the column range. Try this in place of your current formula: =ARRAYFORMULA(IF(ISBLANK(M2:M), "", ROUND(M2:M/G2:G, 2)))


1

Found the solution with Query(ARRAYFORMULA, "select Col1, sum(Col2) where Col1<>'' group by Col1")


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