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.


21

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


8

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

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


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

Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where ...


5

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


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

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

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


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

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


4

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


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       


4

Although regexextract can return an array, you'd need to know how many there are, so this isn't useful in your case. The way to get around this limitation of regexextract is to use regexreplace to get rid of the text you don't want. Like this: =trim(regexreplace(A2, "(^|\s)[^#]\S*", "")) The regular expression matches the words not beginning with # and ...


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 need to use 2 formulas. Create a month column and use the following formula to "display" the month and year: =text(B1,"mmmm YYYY") I assumed that the data was in column B, you will need to adjust accordingly. Copy this formula down the entire column Create a column to hold the labels for the calculation. Put the value for each month in the cells ...


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

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



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