Hot answers tagged

10

Google Docs Upload your existing files. Google Docs accepts most popular file formats, including DOC, XLS, ODT, ODS, RTF, CSV, PPT, etc. So go ahead and upload your existing files. Familiar desktop feel makes editing a breeze. Just click the toolbar buttons to bold, underline, indent, change font or number format, change cell background color ...


4

Google Sheets allows you to protect specific cell ranges so only certain users can edit them.


4

I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells. This means you need an absolute reference: H$1 instead of H1. The dollar sign means this number will not be changed as the formula is propagated to other cells.


3

See if this helps ? =ArrayFormula(query({A1:C,C1:C}, "select Col1, Col2, Col3, count(Col4) where Col1 <> ''group by Col1, Col2, Col3 label count(Col4)''"))


3

You can use the fill handle (small square that appears at the bottom right corner of the cell when it is selected/moused over) to fill the formula down.


3

With the following formula it is possible to exclude a certain range: =ARRAYFORMULA(QUERY({ROW(A1:A60), A1:A60}, "SELECT Col2 WHERE Col1<20 OR Col1>30"))


2

You might look at Docs.com, which is Microsoft's free online service. You won't have to do any kind of conversion with the existing spreadsheet documents.


2

Using the custom number format 0.## achieves most of what you want: the insignificant 0s will not appear after the decimal dot. However, the decimal dot itself will appear, as it's a part of the format. So, you'll have 12.3456 appearing as 12.35 and 12 appearing as 12. (with the dot). In a way, this trailing dot is a feature because otherwise, 12.001 would ...


2

Short answer Spreadsheet functions can't insert rows. The alternatives to insert rows are the following methods Manual Scripting Formula building Proposed method and formulas In this answer, a formula building method and a set of formulas are proposed. In order to make it easy to read, some parameters were referred by using named ranges. Each of them ...


2

Inserting rows No, spreadsheet functions cannot add or remove rows or columns. The two ways to perform these operations are: manually by the user, or by a script. Alternative solution That said, you can create an output of the desired kind with existing spreadsheet functions, without inserting new rows. My approach uses a helper column that can be ...


2

Instead of filter views, I would use the filter command. For example, suppose Sheet1 holds raw data, with A being a character, B the list of "mentioned" chapters, and C the list of "appeared" chapters. You can then add another sheet for "filtered" data, where the user enters chapter number in, e.g., cell A1 and immediately get the list of rows matching that ...


2

Short answer QUERY() allow the use of a subset of SQL but this subset does not include the FROM clause. QUERY() require a single array as the source table, so it could not be used, instead use ARRAYFORMULA(), TRANSPOSE(), JOIN(), SPLIT(), REPT(), FILTER(), COUNTA(), SORT() and the division (/) and concatenate (&) operators to build a "flexible ...


2

This should work: =SUM(ArrayFormula(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area"))) After some clarification this formula seems to have worked: =COUNTIF(ArrayFormula(MMULT(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area"), ...


1

For the highest value you can use: =INDEX(sort(A:B, 2, false), 1, 1) This first sorts and then retrieves the value from the first row and column of the sorted data. Using the opposite sort order in the formula will yield the lowest value: =INDEX(sort(A:B, 2, true), 1, 1)


1

With the new Google Spreadsheet, you are able to add more than 256 columns. To test this, I added this formula in A1: =TRANSPOSE(REPT("A,", 367)) and continued with this formula in A2 =SPLIT(A1, ",") It will raise an error, telling you to add more columns (it will add 300 as a standard): Once added, the split result will expand and show all the data ...


1

So as I was saying... I couldn't find anything that would let me do this that didn't involve installing a full spreadsheet app so I created a simple CSV transposition tool on app engine: CSV Transposer Tool


1

Copy what you want from the Excel document to MS Word first, and then try to paste it into Gmail by coping it from MS Word.


1

As Gmail doesn't preserve formatting, your options may be to: Copy the file to Google Drive and reference that using the "Insert files using Drive" option Insert a screenshot of the relevant portion of the sheet within the email, if you're particular about formatting and the data isn't a lot


1

This is what the command VLOOKUP is for. Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here, A1 is the entry to search for Sheet2!A:C is the range in which the first column (A) is to be searched 3 is the column number from which to take the result (that is, C) FALSE requires exact match, and does not assume the keys are sorted in ...


1

This is a good use case for the SUMIF function. An example is illustrated below:


1

You can use a pivot table to do that. Choose Data > Pivot from the menus, and follow the prompts to set it up. There's an example of something similar here: https://docs.google.com/spreadsheets/d/1VIeA4zAjID0y1jo1FnsbfGA4qmpRgAxMykLXi1pOfhc/edit?usp=sharing


1

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated). ...


1

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet. var range = sh0.getDataRange(); range.sort({column: 2, ascending: false});


1

You can extract the number to another column using REGEXEXTRACT, and then sort the range by that column. For example, suppose this is your C column: +-----------------------+ | 503 in Kitchen & Home | | 85.5 in Bed & Bath | | -263 in debt | | 633.26 in something | | 543 in Outdoors | +-----------------------+ Enter in column D ...


1

Another way of doing it, is by using the AVERAGEIF formula. Formula =AVERAGEIF(A1:B1, ">0") Screenshot


1

Here's a function to do thisif a1>0 and b1>0 then average a1 and b1 else add a1 and b1 =if(AND(A1>0,B1>0),AVERAGE(A1,B1),add(A1,B1)) Obviously you'll have to plug in your own cells for a1 and b1 You can find all the google functions documentation here


1

You are using relative reference, which allows the sheet to assume you want to keep the range size the same, just move it. To keep it anchored, you need to use absolute referencing in your formula. This keeps it from moving. =SUM(D$4:D101) Using the $ before the 4 tells it to lock the top of the range to the 4th cell.


1

You can do this using the copyDown add-on by filling in the right formula in line 2. You can find the copyDown add-on here: https://chrome.google.com/webstore/detail/copydown/lipbbdpmkcbplmfnbholpabinnnkbbda


1

Please try, in B2 of Expense_Overview: =sumif(Expense_Detail!D:D,A2,Expense_Detail!C:C) copied down to suit, or: =ArrayFormula(sumif(Expense_Detail!D:D,A2:A,Expense_Detail!C:C)) You have a treat in store whenever you get around to pivot tables. They are very powerful but incredibly easy to use for all that: Select A1:D7 in Expense_Detail, Data, ...


1

You could use the ImportRange function to sync cell data between two spreadsheets. You could then share the individual spreadsheets to your various students. ImportRange documentation: https://support.google.com/docs/answer/3093340?hl=en I've created an example for you, here is your master sheet: ...



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