3

Instead of date 'TODAY()' use date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'" NOTES: The last " should be placed in a proper place in order to have a text value as the second argument of QUERY. 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis. Try this =ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, "...


2

The fix I used for this issue was to coerce the strings into a number by using ARRAYFORMULA(A1:A+0) in a new column adjacent to the original query (let's say column B). I then used another query on the resulting data set and was able to sum(B) and avg(B) and format them as percentages successfully, as the type of column B was recognized as numeric.


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 ...


2

Use VLOOKUP(). Assuming your lookup table is in H1:I50, you'd put the following in B1: =VLOOKUP(A1,H1:I150,2,FALSE)


1

Logic: In first column we can put current date using =Today() this will give current date. In order to get next date we must increase the date by 1. To get next date we can add =Today()+1 If you want more sequential dates. Then follow this approach: Step 1. Column A put the cell A1 as =Today() Step 2. In column B we want number of days to add so make ...


1

=ArrayFormula({"Result";IF(A2:A="","",COUNTIFS(ROW(A2:A),"<="&ROW(A2:A),A2:A,"<=5"))})


1

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


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 ...


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