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

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


3

You can use the string concatenation operator, &: =IF(A1>A2, "value is " & C5, "value is " & D5) This is equal to, but less verbose than: =IF(A1>A2, CONCAT("value is ", C5), CONCAT("value is ", D5)) As to your follow-up comment, you can concatenate several strings and values: =IF(A1>A2, "value is " & C5 & " right now, but ...


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

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

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

Perhaps something like this: =query(A:C;"select C where A=x and B=y") with x and y replaced by what it is you wish to select. Since sight of your spreadsheet, I am not sure of what you would like where but suggest: insert a column immediately to the right of D in Fleet Log and in E2 enter: =C2&"|"&D2 and copy down to suit. in Sheet5 ...


2

I've found another solution: =JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0)) The % can be any symbol really that isn't present in the list, like a comma, or ampersand, or question mark.


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


2

The third parameter you speak of in the QUERY function, is meant to control the headers. If set to be -1, then the Google Spreadsheet will depict its own choice in choosing headers, based on the data available: I've used the following data set: If set to be 0, then no header will be used, leaving: If set to be 1, then the first row will be used, ...


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

Add the following formula in K1: =ARRAYFORMULA(H:H * J:J) If you have a header in the first row, then do this: =ARRAYFORMULA(H2:H * J2:J) To avoid the remaining zero's, best is to select a fixed range: =ARRAYFORMULA(H2:H250 * J2:J250) Reference Google Drive Help: ARRAYFORMULA


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

You should refer to the sheet name only once, so: =SUM(Sheet1!B1:B5) returns the sum of the range B1:B5 in Sheet1.


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


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

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

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


1

Assuming the data starts in row 1, try in B1: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),)) Out of interest, the formula simplifies a bit if you are retrieving from cells below: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))


1

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


1

ASSUMING the weekdays will always be abbreviated exactly as shown, are the entire cell content (not part of a longer string) and data is in ColumnA, the following may serve: =ArrayFormula(sum(countif(A:A,{"Mon","Tues","Wed","Sun"}))) with the insertion of other search strings to suit.


1

This script will do the same (plus a little bit more). Code function myInterpolation(x, y, value) { if(value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) { throw "value can't be interpolated !!"; return; } var check = 0, index; for(var i = 0, iLen = x.length; i < iLen; i++) { if(x[i][0] == value) { ...


1

You need to prepend your "s with another ": =if(C2= "Aubrey said, ""you are my favorite friend.""", 10, 0) Notice how you get three "s in a row - that's two "s to close the citation, and the last one to end the text string.


1

You can use the =SUMIF as MaryC says, or the =FILTER function that I usually prefer: =SUM(FILTER(F2:F, B2:B = "/Online Computers")) The =FILTER function filters your data so that only rows matching B2:B = "/Online Computers" are included. See the example spreadsheet I've set up, and the Google spreadsheets function list.


1

This is the function you want: SUMIF(range, criterion, [sum_range]) Example SUMIF(A1:A10, ">20", B1:B10) Summary Returns a conditional sum across a range. range The range which is tested against 'criterion'. criterion The pattern or test to apply to 'range'. sum_range - [optional] The range to be added up, if different from ...



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