Tag Info

Hot answers tagged

9

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


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 if(fCell == '') { return false } ...


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


4

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


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

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

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


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

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


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

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

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

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

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


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

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


1

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.


1

Tried to find all kinds of solutions with formulas, but wasn't successful. Therefore I wrote this very small script to do the work for you: function myCalc(range1,range2) { if(range1.length != range2.length) { throw "Both ranges need to be the same !!"; return; } var l = range1.length; var output = []; for(i=0; i<l; i++) { ...


1

My approach would be to "normalise" the data first: =ArrayFormula(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B)) and then you can actually wrap this directly in a QUERY function to produce the desired output: =ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 ...


1

This formula should do the trick: =ARRAYFORMULA(IFERROR(IF(VLOOKUP(A:A;C:C;{1}*SIGN(ROW(A:A));0)=A:A;"Yes";"");"")) Explained from the inside out: the VLOOKUP will lookup column A in column C the IF statement will result "Yes" if something has been found the IFERROR function will ignore empty cells the ARRAYFORMULA will take on complete column ranges ...


1

This is another way of doing it: =COUNTA(FILTER('Survey Data'!F2:F200,'Survey Data'!F2:F200="Divisional Exec", 'Survey Data'!A2:A200="Act")) but, as Jean-Christophe Meillaud stipulated, it yields 1 if the criteria aren't met. The following formula is yet another way of doing things: =IFERROR(INDEX(QUERY(A2:F200,"SELECT COUNT(F) WHERE F='Divisional ...


1

Try this formula in google - spreadsheets =arrayformula(SUM(('Survey Data'!F2:F200="Divisional Exec")*('Survey Data'!A2:A200="Act"))) In Excel - assuming Excel 2007 or later you can use COUNTIFS =COUNTIFS('Survey Data'!F2:F200,"Divisional Exec",'Survey Data'!A2:A200,"Act")


1

You can do this with a simple formula. Given the range shown in your example, insert a new column to the right of column A. In this new column B, write: B136: =A136 B137: =if( isblank(A137) ; B136 ; A137 ) Duplicate the formula in B137 into the cells below it. Let's unpack this: B136 looks to the left for its value (A136). B137 looks to the left for ...


1

The following Google Apps Script (GAS) will achieve your goal automatically: function CUMSUM(array) { // set active spreadsheet var ss = SpreadsheetApp.getActiveSpreadsheet(); // get active sheet var sh = ss.getActiveSheet(); // get data (2D array) var aTest = sh.getRange(array).getValues(); // convert 2D array to 1D array var aData = ...


1

If you're thinking about "loop and if statements", you should look into Google Apps Scripts. Other than that, it seems you're looking for the TRANSPOSE function, in conjunction with the FILTER function. Take a look at the Google Spreadsheets Function List. If you can tell me the purpose of the Purchase column in your example, I might be able to set up a ...


1

Something like this? The ranges are different here but the logic is the same - each cell in your range gets a formula like this (this is cell B2 in the screenshot): =IF($E2=B$1,$A2,"") Which checks to see if the value in the Name column is equal to the column heading of the current cell; if so, it adds the value you put in the HRS column, and if not, it ...



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