Hot answers tagged

37

You can check whether a cell is empty or not by using the following formula: =if(C2="";"The cell is empty";"The cell is not empty")


22

In Google Spreadsheets, OR is a function (with any number of arguments), so you would write IF (OR(C7 = "U.S.A."; C7 = "Canada"); "The Americas"; "Europe") See the example spreadsheet I set up. Also, check the Google Spreadsheets function list. Note that my example code uses ; - you might have to use ,, depending on your locale settings.


19

Always enclose string literals in double quotation marks. = "http://example.com/search.php?Code=" & B2


8

I can use function =IF(COUNTBLANK(cell:cell)=1;"Blank cell";"Not blank") that will be one only of the cell is empty.


8

According to Wikipedia: Users gain one point of link karma when another user votes up their submission and lose a point when a user votes down their submission. Individual comments may also be voted up or down by other users and result in a user's comment karma. Users stop losing karma after their submission reaches 0. This is not the ...


7

Yes, by FILTERing the array: = JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") )) Thus, the JOIN method will only operate on non-empty cells. I have set up an example spreadsheet. Also, check the Google Spreadsheets function list (search for FILTER).


6

It seems what you really want to do is 1) mark certain rows and then 2) sum the marked rows. On any spreadsheet, it makes the most sense to create another column and put a number or character in there. Then use the SUMIF formula to calculate the result. Imagine having a 4 row by 2 column spreadsheet. You want to sum the values in column A if there's a Y ...


6

With this little snippet you can do that. Code function isFormula(startcol, startrow) { // prepare string var str = String.fromCharCode(64 + startcol) + startrow; // retrieve formula var fCell = SpreadsheetApp.getActiveSpreadsheet() .getRange(str).getFormula(); // return false if empty else true return fCell ? true : false; } Usage ...


6

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


5

Short answer Use ISFORMULA() built-in function. Background and Explanation I think I just discovered an undocumented formula in the new Google Sheets. @Pnuts was saying the CELL formula didn't work (partially because it's only available in the new Google Sheets), so I copied the code into a new Google Sheet. An error was raised, telling me my (custom) ...


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 found the function LEN(A1) to be very useful and robust. It also works for cells that contained text that has been deleted. It returns the length of a cell's content and if it returns 0, the cell is empty. IF(NOT(LEN(A1))) or IF(LEN(A1)=0) to check for empty cells IF(LEN(A1)) or IF(LEN(A1)<>0) to check for non-empty cells I prefer the fist ...


4

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


4

I've searched the internet (Google, search tools, custom range) for references of the ISBLANK function pre-dating the OP's question. The ISBLANK function already existed during the time of asking: ISBLANK reference before 01/12/2009 Therefore this function will suffice: =IF(ISBLANK(A2)=TRUE;"Blank cell";"Not blank") There is however one thing to take in ...


4

This works OK for me: =concatenate("http://example.com/search.php?Code="; B2)


4

The problem you have is that the data row reference changes when you drag-copy the formula down. It needs to be made static. So instead of sheet2!A1:K you need to write sheet2!A$1K or in this case (as you're starting from row 1 anyway), you can use: sheet2!A:K


4

I found three solutions for you. Formula =INDIRECT("DATA!A"&A1) Google Apps Script function dataSubset(data,subset) { var output = []; for(var i=0, iLen=subset.length; i<iLen; i++) { for(var j=1, jLen=data.length+1; j<jLen; j++) { if(parseInt(subset[i]) == parseInt(j)) { output.push(data[j-1]); } } } return ...


4

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


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


4

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

The pure spreadsheet answer is to create columns D and E to display your information. An example row: A3: text B3: (enter USD if known) C3: (enter LBP if known) D3: = if( isblank(B3) ; C3/1500 ; B3 ) E3: = if( isblank(C3) ; B3*1500 ; C3 ) The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the ...


3

Delim means delimiter array_1 means the cells you want to use. =join(",",B1:B4) Will put a comma between each value from cells b1 to b4. The result appears in the cell where you put the formula. Note: the cells must be in the form as 1xN or Nx1 array. In other words all in the same row, or all in the same column.


3

You could create a shared Google Document, put equations in the document (via Insert->Equation) and use the chat window that is available in all Docs applications. It won't allow the equations to appear inline with the chat, but at least you'd be able to see them and discuss...


3

I personally use MathIM, which is an online chat room which parses everything between $$'s in your message as TeX. It's perfect for talking to my friends when I need to use math equations. It seems to be exactly what you are looking for :)


3

Figured it out... took me a while... INDIRECT() has to wrap the entire reference. In order to achieve what I needed, the formula looked like this: =INDIRECT("Sheet2!A"&A1)


3

If you don't want to precalculate the subtotal of off days, you can use an array formula: =arrayformula(SUM((A:A="Jess")*(B:C="OFF")*1)) A:A="Jess" returns a bunch of TRUE and FALSE, in your sample, it would be {TRUE, FALSE, FALSE, TRUE, FALSE} B:C="OFF" returns another bunch of TRUE and FALSE, in your sample, it would be {FALSE, TRUE; FALSE, FALSE; ...


3

I'm not sure if you necessarily require a Google Apps Script solution; the tags you have used suggest that a spreadsheet formula might be adequate. If this is the case, then it is definitely possible to sum only rows that are not hidden with SUBTOTAL. For example: =SUBTOTAL(109;A:A) will sum all cells in column A that are not hidden. Where 109 is ...


3

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


3

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


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



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