Tag Info

New answers tagged

3

The operations are mostly done on the servers as to stay caught up with other participants and I'll assume long calculations are no exception. If you're looking for powerful spreadsheets I'd recommend not using cloud services for reliable processing speeds. And if the purpose of the spreadsheet is importantly for sharing with others there is more ...


2

I use this formula to convert the date time values IFTTT spits out to a useful format: =TIMEVALUE(SUBSTITUTE(A1," at ", " ")) + DATEVALUE(SUBSTITUTE(A1," at ", " ")) Where A1 is the cell containing the date string This gives you a decimal number that you can format as "Date Time".


2

In case you want to count the occurrences of 'X' (text) with 'January' in adjacent cell, try: =countif(filter(A:A, B:B="January"), "X") In case 'X' is actually a numeric value, and you want to sum all those numbers who have 'January' in the adjacent cell, try: =sumif(B:B, "January", A:A)


0

If you are looking to do this programmatically using formulas, you could put the following in each cell on column C: = IF ( B1 = "January" , A1 , 0 ) Then wherever you need the summed value to appear enter = SUM(C1:C500) Where 500 is the maximum number of rows you are enumerating. You could also hide the "C" column if you wish.


1

If I understood correctly, a combination of filter (which filters out the empty cells, keeping only the non-blank cells) and transpose, should work: =transpose(filter(B2:2, len(B2:2)))


0

None of the previous answers worked for me, because I use a form as input. You can't add formulas on the form-sheet. Here is my work-around. Reading the above, the Pivot table needs a column with month to select on. You can use a different criteria, like week or fridays. Or John, Doe, Foo or bar. Whatever you want to 'group'on. In my case: date is in B, ...


1

In Excel You can also use this version =SUMIF(OFFSET(A1:C15,ROW(A1:C15)-ROW(A1),0,1),"<9.99E+307") confirmed with CTRL+SHIFT+ENTER assuming your matrix is in A1:C15 That has a small advantage over MMULT - it still works if some of the cells in A1:C15 are blank or don't contain numbers However you can amend the MMULT version to work with a partially ...


1

Try: =AVERAGE(FILTER(4:4, LEN(2:2))) and see if that works for you. Explanation FILTER(4:4, LEN(2:2)) This part filters row 4 so that it has the same amount of cells as the non-blank cells in row2. Whether a cell (in row 2) is blank or not is checked with the len function, that will return 0 (no LENGTH) for empty cells. AVERAGE(FILTER(4:4, ...


-1

Try using Doctopus it will create multiple copies for individuals. Normally used in classroom setting to assign work.


0

An Excel Solution with One Formula for Each Sum Value It sounds like you want the OFFSET function. I'm going to use MATRIX() as a stand-in for whatever function you have that is returning a matrix. If you want to show sums for the first 3 rows of that matrix, the formulas would be as follow: =SUM(OFFSET(MATRIX(),0,0,1) =SUM(OFFSET(MATRIX(),1,0,1) ...


0

If you select the three rows in Column A and use the Format / Merge Cells / Merge All applied option, you get something which looks like that. There is an example of how it looks in this sheet: https://docs.google.com/spreadsheets/d/1Sg-Rn_ZcNic8rpFek9DTlKtU-XuS4rukeZoyeQ4aLJA/edit?usp=sharing The disadvantage is that it only works when you don't ...


2

Maybe give this a try: =ArrayFormula(sort(vlookup(query({row(A:A),sort(A:C)},"select max(Col1) where Col4 <> '' group by Col4 label max(Col1)''",0),{row(A:A),sort(A:C)},{2,3,4},0),1,0)) Explanation {row(A:A),sort(A:C)} will create a new (virtual array), consisting of 4 columns: 1 column with the row numbers + 3 columns of the original table ...


0

See the example spreadsheet. Sheet Countries contains source data (instead of prices and products, I've used countries, population and other data, but the concept is the same). Sheet Dropdown contains the example. In that latter sheet, cell C1 and E1 are dropdown menus (use the Data→Validation… menu to see how data validation works). Then, in cell A2, you ...


2

This will do: =SUM(INDIRECT(ADDRESS(ROW()-11;COLUMN();4) &":"& ADDRESS(ROW()-1;COLUMN();4))) Just paste it any cell you like and it will sum 10 cells above it, with no changes to the value. The ADDRESS function returns a string with the name reference of a cell. We join two references with a ":" between them, giving us a string like "B5:B15". We ...


0

With the new version of Google Spreadsheets, Google App Scripts is now deprecated. You can use the Power Tools of the Google Add-on Gallery and use the Split function. It works well.


0

Take a look at this. I've used the Custom Formula =AND(ISDATE(B2),B2>=NOW()) to conditionally format the range B2:F12. ISDATE() avoids formatting cells with contents like "This isn't relevant", which Google Sheets considers to be >= NOW() — Boolean operations in Google Sheets do no type checking. I deliberately haven't formatted the selected cells red, ...


1

I had a similar problem and found a good solution. Copy the table to some simple text editor that supports regex. I used notepad++. Then insert the decimal separator before the numbers. Then you can copy the table to the spreadsheet. E.g. if you have 3 digits without a decimal separator in front, you search replace using (\d\d\d) and .\1.


0

agtover's comment to the question works fine: Extend the function range to D3 (where the 'Amount' headline is), and then whenever a new row is added, only the end of the function range is updated.


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

If you are working in a field where that degree of decimal precision is important, then perhaps Google Sheets (where functional changes happen regularly, and many are not notified to the users) is not the best tool to use. All applications round numbers at some point. If you need engineering precision in how this is done, then you may need to investigate ...


0

With the following formula you can accomplish that. Formula =QUERY(DATA!A1:B4, "SELECT A, B ORDER BY B DESC LIMIT 3") Explained The QUERY formula selects the two columns and orders them descendingly according to column B. Then it limits the result to three rows (top 3). Screenshot data result Example I've created an example file for you: Top 3 ...


2

The solution is quite simple. Press this button to increase the number of decimals: Now it will show the correct amount of decimals: Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest.


5

To update currency conversion rates on a daily basis, for the Euro to $US for example, do this: =GoogleFinance("CURRENCY:EURUSD";"price") That formula goes in the cell where you want the quote to appear. Instead of "price", you could use "close", if that's what you want for the day prior.


0

You can use: =GoogleFinance("ticker symbol","price") It will pull USD, GBP, CAD, updating the daily quote.


2

I thought that there might be a cap on historical data, based on some variant of ten years, e.g. rolling quarterly, since your last available date was 1 October 2006. I checked for myself, for the same forex pair mentioned, Euro to $US. Using this formula in Google Spreadsheet, I had no problem retrieving pre-2006 data (50 historical data points for Euro to ...


1

You can sum the values used in the HYPERLINK formula. Formula =SUM(ARRAYFORMULA(VALUE(RIGHT(A1:A5, LEN(A1:A5)-1)))) Explained The LEN formula determines the length of the string. The RIGHT formula allows you to extract characters from a string from the right side on. It uses the length of the string minus 1 (the $). After that, the returned text in ...


0

I was able to reduce the formula and add ARRAYFORMULAE to make it work with ranges. Formula =ARRAYFORMULA( IF(ISBLANK(A:A), "", SUBSTITUTE( ARRAYFORMULA( A:A & IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & "utm_campaign=" & B:B & "&utm_medium=" & ...


1

I do not know how to solve this problem without scripts, but there is a simple work around. When you update prices, you can drag them elsewhere in your sheet (e.g. below, in the form of a price record). The values on the 'Sales' sheet will stay linked to the original price, and the formula you enter for sales will be the same. If you choose this work ...


2

The solution was to select all the cells (I can't find a Select All menu item, but on OS X Command-A worked), then choose Copy, then Paste special/Paste values only. Bingo: all the cells whose values depend on the ImportHTML() in A1 now have their values pasted.


1

Try resizing your column/row so that it matches the image dimensions. If you can't eliminate white space, your source image probably includes some kind of white padding; try inserting the following image instead: =IMAGE("http://www.buzzinbees.com/images/logos/buzzinbees.rect.2040.png") and look at the resulting cell once you've resized it to match image ...


0

You cannot sum the contents of cells containing hyperlinks, however, you can make your hyperlinks retrieve data from other cells. If you don't want to see these other cells, just hide the corresponding columns. For instance: | A | B | C | -------------------------------------- | http:…|$25.00 | =hyperlink(A:A,B:B)| You can now sum ...


-2

To copy your formula to the whole column, select the cell containing your formula and simply drag down the blue square at the bottom right of the cell till you hit the last row in your column. When you insert new rows at the bottom, you need to copy the previous row into these new rows, so that your formula carries over. If you cannot expect users to copy ...


1

You cannot modify the data provided fetched with IMPORTHTML: if you do, you'll indeed either erase it all (if you modify the cell containing the IMPORTHTML function), or a reference error. However, you can achieve your goal by referencing the imported data from another sheet. Let's assume you have your IMPORTHTML call within sheet Sheet1. You can create a ...


1

Having two onEdit scripts isn't gonna work. So try to merge the two scripts into one... function onEdit(e) { var s = e.source.getActiveSheet(), sheets = ['In', 'Out'], ind = sheets.indexOf(s.getName()); if (ind === -1 || e.range.columnStart < 2 || e.range.columnStart > 8) return; e.range.offset(0, 9 - e.range.columnStart) .setValue(new ...


0

With the following piece of code you can subtract military formatted times. Code /** * Subtract Military Formatted Times * * @param {cell} checkin The time at which you have checked in * @param {cell} checkout The time at which you have checked out * @return The time difference in Military Time Format * @customfunction */ function myTime(start, end) ...


1

With the range set to: B3:O1000 Try: =AND(ISBLANK(B3),INT($A3)=INT(today())) or even shorter: =AND(ISBLANK(B3),$A3=today())


1

In order for you to do that, you can either press Ctrl + ` or select from the menu View > Select all formulae. Now you are able to select the range you want to copy. Screenshot menu before after


1

Here's a script that should do what you need: var MilitaryTime = function(value) { this.timeValue = parseInt(value, 10); this.hour = Math.floor(this.timeValue / 100); this.minute = this.timeValue % 100; this.totalMinutes = this.hour * 60 + this.minute; } MilitaryTime.fromMinutes = function(totalMinutes){ var hours = Math.floor(totalMinutes / 60); ...


2

With the following little script you can do that. Code function onEdit(e) { var r = e.range, c = r.getColumn(), v = e.value; if(v == "ACTIVE" && c == 6) { r.offset(0, 1).setValue(new Date); } } Explained The onEdit trigger is only fired if column F is selected (c == 6) and if the value of the list is (changed to) ACTIVE (v == ...


1

Only after having requested that the documents were to be shared with us, was I able to determine what's going on. The response sheet is new Google Sheet proof: The sheet, in which you want to pull the data in, doesn't have this mark. You are trying to import the data via the complete URL (new Sheets style) from within an old Google Spreadsheet. This ...


1

This should allow you to get the last element in the array generated by split(): =ArrayFormula(index(split(A1,char(10)), columns(split(A1,char(10)))))


0

Have a look at this link https://support.google.com/docs/answer/2839737?hl=en I suppose you should not edit the spreadsheet directly. Alternatively you need to edit the Form and the changes will be made to spreadsheets automatically.


-1

You can select multiple cells, type in your value, then hit Ctrl-D to fill down, or Ctrl-R to fill right.


1

Wow, a year later I had this issue again. Finally realized that the issue was Vimium—once I disabled it on docs.google.com everything was fine. Seems kind of obvious, but for some reason I had figured that wasn't it. I guess I was unconsciously assuming I had already disabled Vimium in Google Docs because everything else worked.


0

The quick start tutorial at https://developers.google.com/apps-script/quickstart/macros shows how to write a JavaScript function in the App Script editor and call it from a Google spreadsheet. In this case the function can use a regexp such as /\d+@[^,]+(,\d+@[^,]+)*/ That means one or more digits followed by an @ and one or more non-comma characters, ...


0

If you want to create a drop-down list of your locations, see the instructions on data validation at https://support.google.com/docs/answer/186103 . Presumably you'd use it with a formula to convert location names to numeric values. If you want to create a custom function to do that conversion, see the quick-start tutorial at ...


0

Perhaps the following piece of code will work for you. Code function onEdit(e) { var str = e.range.getValue(); if(str.substring(0, 5) == "_time") { var v = str.match(/\d+/g); if(!v) { getTime(e); } else if(!v[1]) { getTime(e, v[0], '0'); } else { getTime(e, v[0], v[1]); } } } function getTime(e, hrs, min) { ...


-1

Disclaimer: I work for Ragic As Ayhan said, Google spreadsheets doesn't have a direct way to do this. you might want to try using an actual database application like Ragic.


1

In a google spreadsheet, something like this should work: =average(array_constrain(filter(B:B, A:A=1),10,1)) or alternatively: =average(query(A:B, "Select B where A =1 limit 10",0))



Top 50 recent answers are included