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

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

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

Please try, 1 in A2 and in A3 copied down: =if(mod(row(),7)=2,A2+1,A2)


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

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

I found a way to do it - there may be a better way, but this is what I came up with: Assuming the data is in A1:B10 and $C$1 contains the key to look for: =FORECAST($C$1, OFFSET(B$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1), OFFSET(A$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1)) In detail: FORECAST does a linear interpolation, but it assumes a straight line. So we ...


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

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

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

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


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.


1

At this time there isn't a built-in function that gives the number of sheets, so you have to think around the alternatives to Extend Google Docs, Sheets, and Forms with Apps Script. It's worth to say that sheets doesn't have an inherent number, so you should determine how do you will assign a number to each sheet. One way is to assign a numerical prefix as ...


1

It sounds to me like you have a fundamental design flaw if each month is a new sheet, and there is nothing on the sheet to show the current month. I would suggest putting all the data onto one sheet, and then adding per-month extracts onto other sheets using the various summarisation and extraction tools (pivot tables etc).


1

Please try something like: =query(importrange("https://docs.google.com/spreadsheets/$$$$","Applicant Form Response!A:G"),"Select * where Col7 = 'Pass' ") where $$$$ is whatever is appropriate to complete the URL for your source (access to which you will need to grant).


1

For a conditional running average, assuming all entries in A2:A are grouped: =ArrayFormula(IFERROR((SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-HLOOKUP(0,SUMIF(ROW(A2:A),"<"&ROW(A2:A),B2:B),MATCH(A2:A,A2:A,0),0))/(ROW(A2:A)-MATCH(A2:A,A2:A,0)-ROW(A2)+2))) Before the update to the newest version of Sheets a number of months ago, it would have ...


1

I used this to solve my problem. =IF(ISTEXT(VLOOKUP($A6,responses!$D:$M,3, FALSE)),"Yes","No")


1

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg: function onEdit(e) { var editRange = e.range; var sheet = editRange.getSheet(); if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1") { var updateRange = sheet.getRange("A1"); var newValue ...



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