Hot answers tagged

9

Use lookup instead of if: =lookup(B7, {"#000000","#00ff00","#ff0000","#ff9900"}, {"Not applicable", "Read","Unread","In Progress"} ) Note the second parameter must be a sorted list.


8

Short answer The problem in the examples provided are the parenthesis. Apply them properly. Explanation IF() function should have two parameters and optionally a third one. IF(logical_expression, value_if_true, value_if_false) The specific problem with the first example provided is that the outer IF() has too many parameters. the second example is ...


6

You may use the Web version of Keep (https://keep.google.com) and copy/paste all items directly there and it will create a checkbox on each line you pasted. It will sync automatically with the mobile app.


4

You could use a simple formula to build a string defining the Range, name that location, and then access it with the INDIRECT Function in your formulas. E.g. for your question above. Put in any cell (e.g. B3) a formula like: = "Sheet!$A$1:$A$" & B2 Name Cell B3 as "items" as a named range. Then whenever you would have used "items" in a formula just ...


4

Short answer Use =SUM(INDIRECT("D2:D")) Explanation Google Sheets will update A1 notation references when rows or columns are inserted, so if you need that a reference never changes, instead you have to use a string and the INDIRECT() function to convert it to a reference. References INDIRECT - Google Docs editors Help


4

In Google Sheets, you need to wrap small in arrayformula so it produces an array: =average(arrayformula(small(D2:BD2, {1,2,3,4,5,6,7,8,9,10}))) There is an alternative solution, which is shorter and scales better if you will need 100 smallest instead of 10 smallest numbers: =average(array_constrain(sort(D2:BD2), 10, 10)) Here, sort sorts the array in ...


4

Your function works correctly, in the sense that it gives the probability density function of the χ²-distribution. It returns the same results as =CHISQ.DIST(x,v,FALSE) in Excel 2013. Note that "false" here means "not cumulative". The LibreOffice function =CHIDIST(x;v) and (old-fashioned) Excel function =CHIDIST(x,v) return the right-tailed cumulative ...


3

You can filter using a regular expression. Use the options Filter by condition > Custom formula is... =not(regexmatch(N:N, "(?i)facebook|pinterest|twitter")) to see only the strings that do not contain one of the above. Here N is the column you're filtering, and the regular expression means: the string contains match one of words facebook, pinterest, ...


3

Short answer Add a new sheet. Add the following formulas to the new sheet Cell A2 -> Headers =OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0) This could be repeated several times, one for each header column, just change the last parameter accordingly. Cell B2 -> data =OFFSET('Form responses ...


3

Short Answer At this time, Google Sheets doesn't have a feature to assign a name to a variable defined by a formula instead of a cell or range reference. To use a formula with these kinds of variables the alternative is to use custom functions. Custom functions in Google Sheets Custom functions are defined in Google Apps Scripts bound projects or Google ...


3

You need =E$1 there. (Or, =$E$1 if the formatted range spans multiple columns.) Putting E1 would compare cells to the string "E1", which is not what you want. Putting =E1 compares the first cell in your column range to E1, the second to E2, the third to E3, etc -- because the reference is relative. The dollar sign makes it an absolute reference.


3

You don't need a script: the Filter command (which is different from filters and filter views) does this easily. Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter =filter(Master!A:Z, month(Master!B:B) = 1) Here A:Z are the columns of Master sheet (of course there may be more, you can use ...


3

Partial formatting of cell text is a recent addition to Google Sheets, and it is still pretty fragile. The following works: Select a cell Press Ctrl-C (or Command-C) Select another cell Press Ctrl-V (or Command-V) Other methods, described below, are not as successful. If one double-clicks a cell, selects all of its content, and copy-pastes that, the ...


3

Here is a shorter formula that is equivalent to yours, using yearfrac: =yearfrac(date(year(A1), 1, 1), A1, 1) However, the formula you have is inconsistent with your description, since it returns 0 for 1-1-2015, representing that at the beginning of that day, no time has elapsed since the beginning of the year. To have 1/365 for 1-1-2015 and 1 for ...


3

This worked for me when I tested it with your sample strings: ="AND(A="&SUBSTITUTE(A1,","," OR A=")&")" It basically replaces each comma with the or part of the string.


2

An alternative to double clicking / pressing F2 on a cell is to paste the text into the formula window (next to "fx" above the sheet). Note that neither this nor other methods described will preserve formatting (font color, font face, etc), which may or may not be a plus.


2

If you use the Query Function then you don't have to worry about the list growing and not having copied the sum formula down far enough. If names are in A:A and amounts are in B:B, then in D1 enter: =QUERY(A:B, "select A, sum(B) group by A") There is a much more thorough answer here, with screenshots even.


2

Lipis' code was not working for me, so here's an update. This code: works with current version of Google Spreadsheets correctly addressing array elements checks whether the values are numbers has a switch to consider zero values or not Code: /** v - Values range w - Weights range z - count zero values? */ function weightedAverage(v, w, z) { var ...


2

It doesn't work with arrayformula - importdata, importxml, and split will not work it unfortunately.


2

The event object helps here, specifically its changeType property, which tells the script what kind of change happened. If this change is INSERT_ROW, and the name of sheet matches the one you want, then an email is sent using MailApp. function notify(e) { var sheet = SpreadsheetApp.getActiveSheet(); if (sheet.getName() == 'Tracked Sheet' && ...


2

It's not possible without a formula. Separate time storing from time displaying by using one cell to store the time and another to display it in the desired format by using a formula like the following =HOUR(A1)&":"&TEXT(MINUTE(A1),"00")


2

Good question: I just looked expecting to find an option under Format > Number > More Formats > Custom Number Format but couldn't see anything that would work. If you do end up doing it with a formula, then the easiest approach may be to use helper columns, and something like =if(hour(E6) > 12, hour(E6)-12, hour(E6)) (Not the answer you want, I know, ...


2

Short answer From the question After the user submits their choice on that page of the survey, I want the next page to display text that refers to their choice. This is not possible with Google Forms. Explanation Google Apps Script could be used to create, access, and modify Google Forms and to read and edit form responses but not to control the ...


2

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1. ...


2

The query would be like this: =query(A:D, "select * where C > timeofday '15:00:00' ") (or >= if you want to include 3 PM sharp) Here, the single-quoted string 15:00:00 is preceded by the keyword timeofday, indicating its meaning. This is similar to how date strings are preceded by date. Note that the format of query string is fairly rigid: it has to ...


2

You can put a comment on any cell, and other people can reply your comment. After the discussion is over, you can resolve it so it get archived. On the top right hand corner, there is a "Comments" button. You can use it to browse both active and resolved comments. You can also delete discussion threads you no longer want to keep.


2

Short answer Alternative 1 Instead of {"First Value;"Second Value"} write ={"First Value,"Second Value"} in cells D2 and D3 in the example provided in the question. Then, instead of using a category names reference of the form D2 use the D2:D3 reference form. Alternative 2 Instead of {"First Value;"Second Value"} write First Value;Second Value in cells ...


2

Here is one option for you =sum(filter(A2:A, match(B2:B, split(D2,";"), 0))) where D2 is a ; delimited array, i.e. "movie;video games" split(D2,";") convert D2 to an array: {"movie","video games"} test condition match(B2:B, split(...), 0) checks for B values that is "movie" or "video games" filter(A2:A, match(...)) gives you the corresponding A values ...


2

If you did this just for the convenience of your employees, then filter views would do the job nicely. But filter views don't offer any data protection: if someone can see the filter view, they can see the entire sheet by turning off the filter view. To actually protect the other rows from being viewed, you can create a separate spreadsheet for each ...


2

You can do a count for each month by transforming an array formula to only count based on that month: =COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"01") change the "01" for each month so february would look like this: =COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"02")



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