Tag Info

Hot answers tagged

5

This formula will do the job: =regexextract(A1;"@(.*)") If no @ is found, the original value is shown. The =regexextract function (documentation) executes the regular expression parameter (@(.*)) against the value of cell A1, and returns the matching group.


4

I would use the following formula to obtain an URL. Formula =IF(ISEMAIL(A2), REGEXEXTRACT(A2,"@(.+)"), IF(ISURL(A2), A2, IF(ISURL(REGEXEXTRACT(A2,"@(.+)")), REGEXEXTRACT(A2,"@(.+)"), "No valid entry" ) ) ) Copy / Paste ...


3

Two options to try: =IF(ISNUMBER(A2),INDEX(SORT(SAC!F:F,SAC!E:E,0),MATCH(A2,SORT(SAC!E:E,SAC!E:E,0),-1)),) =QUERY(SAC!E:F,"select F where E >= "&A2&" order by E limit 1",0) It's a shame that VLOOKUP doesn't accept the -1 sort parameter, otherwise this would work: =IF(ISNUMBER(A2),VLOOKUP(A2,SORT(SAC!E:F,1,0),2,-1),)


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


3

You can wrap the ISBLANK() with a NOT() it basically switches what ever is in it to the opposite of its original result. =NOT(ISBLANK(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

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


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


3

If the data starts in row 2, then try: =ArrayFormula(IF(A2:A="A",SUMIF(IF(A2:A="A",ROW(A2:A),ROWS(A:A)+1),"<="&ROW(A2:A),B2:B),))


3

Assuming your data is in A1:A, try in B1 =query(ArrayFormula(iferror(regexextract(A1:A, {"^(.+)\s", "\s(.+)$"}))), "select Col1, count(Col2) where Col1 <>'' group by Col1")


3

The following custom number format mask will produce the desired results without losing the accuracy of the underlying value. [>999999]0.0,,\M;[>999]0.0,\K;0       


3

In cell G9 of the spreadsheet you shared, I entered this formula: =arrayformula(if(len(B9:B33)*len(C9:C33)*(C9:C33>B9:B33);if(timevalue(B9:B33)<timevalue("17.00.00"); mod(C9:C33-time(17;0;0);1)*1440; mod(C9:C33-B9:B33;1)*1440);)) It will only output a result when there are values in col B and col C and when col C > col B. See if this helps ?


2

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.


2

This formula will work to keep blank cells from returning "0" to another cell. =IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero") Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage ...


2

In Dashboard D36 please enter: =sumif(log!C:C,A36,log!B:B) (should result in the 19 you mentioned) and then copy up to D4.


2

I think you are getting the correct answer but .2857 is being rounded down to less than £1! (ie it is just the formatting).


2

If your email address (bob@example.com) is in A1 =index(split(A1,"@"),0,2) = example.com =index(split(A1,"@"),0,1) = bob You are splitting on the @ and than using index to select which column you want from the split.


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


2

You can refer to cells in the DSUM criterion. The problem with your query that you put a function inside of a string instead of appending its result to a string. That is, instead of ">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)" you need ...


2

I would use VLOOKUP instead of DGET; it has simpler syntax and works with ARRAYFORMULA. Example: Sheet2 columns A and B +---------+-------+ | User Id | Name | +---------+-------+ | 4444 | Grace | | 1111 | Jon | | 2222 | Tim | +---------+-------+ Another sheet has numbers 1111, 2222, 4444, 2222 in cells A2-A5. Entering in B2 the formula ...


2

The solution, as I understand it, is as follows. By setting a FILTER formula (array & condition) you create the first step, the "what are we looking for, specifically" then that formula gets wrapped in a SUM formula which adds the array values. So, in the case above =SUM(FILTER(B:B,A:A="June")) this formula is looking for the values, all of them, in ...


2

The problem is that "11:00:00 AM" is not a time value, it's just a string with characters 1, 1, :, 0, and so forth. You can convert it to a time value with timevalue function: =A1<timevalue("11:00:00 AM") or better yet, define the time directly with time =A1<time(11, 0, 0) The second approach is locale-independent; it does not rely on ...


1

Try: =ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9)))) or: =ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9)))) or also: =ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))


1

I think you need the time() function. e.g: A1= hour B1 = minutes in C1, try: =TIME(A1, B1, 0)


1

Another way of doing it, is by using the AVERAGEIF formula. Formula =AVERAGEIF(A1:B1, ">0") Screenshot


1

Here's a function to do thisif a1>0 and b1>0 then average a1 and b1 else add a1 and b1 =if(AND(A1>0,B1>0),AVERAGE(A1,B1),add(A1,B1)) Obviously you'll have to plug in your own cells for a1 and b1 You can find all the google functions documentation here


1

At this time Google Docs (documents) doesn't include a feature to do calculations. An alternative is to use an add-on or a Google Apps Script to extend the built-in features of Google Docs. References Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help


1

Let's assume your spreadsheet has the id 1wJR0uH0v0KPex4uoma9Gy2IHCW5JnvQ-QWa6V1GtN5g (you can obtain the id of your spreadsheet by looking at its URL in your browser: it is between …/spreadsheet/d/ and /edit…). Let's assume your spreadsheet contains the sheets: overview test1, test2, test3 hidden (you'll add this last worksheet and hide it before you ...


1

If you want to use forms there is a time picker element to select time. Otherwise, you don’t have a time picker. You got 2 options: You can create 2 columns includes hours and minutes and add a drop down, then generate time from those. You can add time correction to field. : between hour and minute concert valid time field.


1

Maybe something like: =IFERROR(index(B:B,match(F4,A:A,0)),index(B5:B100,match(F4,A4:A99))) where F4 contains the 6/10 value.



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