Tag Info

Hot answers tagged

26

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


17

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


11

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.


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


6

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


5

This may help you: Google Spreadsheets conditional formatting based on another cell's content How to enter your Apps Script into your spreadsheet: http://code.google.com/intl/de/googleapps/appsscript/guide.html


5

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


5

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


5

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


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


3

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


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

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

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

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

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


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


2

You can try Pidgin (http://www.pidgin.im/) is an IM client for windows, MacOS and Linux. Then install this pidgin plugin: pidgintext


2

You can find them using find and replace if you go to view -> all formulas (ctrl+') and then do you "round( " find this will at least cut down on the manual labor of it all I couldnt find a way (using find and replace to search for wild card value though EDIT: Try out the script below REF: ...


2

The best way I can think of to accomplish this is with importRange function. Here's the general idea. You have three spreadsheet documents. Doc1, Doc2, Doc3. Doc1: Range A1:A20 contains list of items you want to validate against Doc2: Range B1:B20 contains list of items you want to also validate against Doc3: Range C1:C20 contains range you wish to have ...


2

It is not possible to use formulas for conditional formatting based on this Google Groups Discussion.


2

This seems to be pretty much perfect: http://www.twiddla.com/ It even has support for LaTeX! http://www.skrbl.com/ seems pretty good too as a slightly more basic alternative. Also, here's quite a good list of interactive whiteboard related links, many of them whiteboard webapps: http://www.shambles.net/pages/staff/intwhiteb/


2

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


2

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


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

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)


2

You can filter out the unwanted results by using the FILTER function: =UNIQUE(FILTER(A1:A; A1:A <> "No explanation/don't know"; A1:A <> "To far"; A1:A <> "Too costly")) Check out this example spreadsheet.


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

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



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