Tag Info

New answers tagged

3

In N3 I entered this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset 1",0), vlookup(query(query(query(A4:D, "select D, A, sum(C) group by A, D"), "select max(Col3), Col2 group by Col2 label max(Col3)''"), "select ...


2

Doing calculations on date/times is tricky, because there are a lot of other parameters that you usually don't think about. Are the two times in the same time zone, for example? Or on which date are the two times? I see that you want 22 hours + 22 hours to be displayed as 1 day + 20 hours, which sounds reasonable enough, but that assumption fails as soon as ...


3

I was able to reduce the formula to the one below. Formula =ARRAYFORMULA( IFERROR( E3:E6 + VLOOKUP(G3:G6, temperature, 2, 1) + VLOOKUP(H3:H6, alkalinity, 2, 1) + VLOOKUP(I3:I6, calcium, 2, 1) - 12.1, "Missing values" ) ) copy / paste =ARRAYFORMULA(IFERROR(E3:E6 + VLOOKUP(G3:G6, temperature, 2, 1) + VLOOKUP(H3:H6, ...


2

You can wrap the ISEMPTY() with a NOT() it basically switches what ever is in it to the opposite of its original result. =NOT(ISEMPTY(A2)) Its checking if A2 is not empty. If it has something in A2 it will give a True result. NOT - Docs editors Help


3

Filter() should do that. Let's say the sheet with the 'source data' is called Sheet1, then on the second sheet (where you want the level1 names), in cell A1 try: =filter(Sheet1!A:A; len(Sheet1!A:A)) and repeat for all the other sheets and ranges..


3

In addition to previous posts, I think I would go for a query function. Although not that short , it will eliminate the need 1) to drag the current formula down (newly added rows will 'automatically be added to the output of the query) and 2) to have the unique formula to populate col A on the count sheet. =query(Incidents!B2:C; "select B, count(C) where ...



Top 50 recent answers are included