# Tag Info

## Hot answers tagged google-spreadsheets

66

Oh, you just multiply by 24 then format as a number. I guess durations are numerically stored as the number of days. Simple.

65

Recommended way is to use TEXT() function. Quick summary on how you would use it in your case: =TEXT(5,"000") Would output: 005 Of course you would substitute the literal 5 with a reference to another cell where the source number is.

33

Use Substitute: =SUBSTITUTE(A1,CHAR(10),";" & CHAR(10)) &";" CHAR(10) is the Line Return. Make sure the wrap text is on for the target cell

13

The freeze menu item now lists the first two rows/columns or all the way up to the currently selected cell: This can also be done by dragging the frozen column handle at the top of the column:

12

The suggested answers work well for small sheets but I had thousands of rows and using the mouse or the keyboard to select them was simply too time consuming. The ARRAYFORMULA method works but it's complicated, forces me to rewrite formula style and consider possible errors). The solution is so simple it can be done in 2 seconds: Write your new ...

12

If you just want display changes you can apply the custom number format "000" to the cells. Select the cells, Click on Format > Number > More Formats > Custom number format.... From Docs editors help: A digit in the number. An insignificant 0 will appear in the results.

9

For Google Spreadsheets Google Spreadsheets has these cool already-builtin regex formulas REGEXEXTRACT, REGEXMATCH, REGEXREPLACE We use the first one to extract mail adresses. Put this formula in a Google Spreadsheet cell: =iferror(Regexextract(A1;"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}");"") A1 is the cell where the string to check (mail body in your ...

9

In Google Sheets: =INDEX(SPLIT(A1,","),2) Where the 2 is the index you want. In Excel: =TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(2-1)*999+1,999)) Or this array formula: =INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),2) Being an array formula it needs to be confirmed ...

8

In a quick test, I was able to accomplish what it sounds like you are wanting with the following. Using CTRL + Left Mouse Click, Highlight one rows worth of cells that you want included in the validation; F, J, and N in your example. Go to the Format menu and choose Conditional Formatting In the Conditional format rules pane, change the "Format cells if..." ...

6

Create a blank Column (D) and use "CONCATENATE": =CONCATENATE("0049",C1) Drag that down for each adjacent cell in Column C and it will add 0049 as a prefix.

6

I found all of these solutions very frustrating and confusing also. I will warn you though, this will replace whatever is currently in the cells, but as it is a formula this should not be a problem. For me it was simple. Click the cell whose formula you want to copy once (select it) Copy the cells contents (Ctrl+C on Windows, cmd+C on macOS) Hold Shift+...

5

After you write your forumla, you can double click the bottom, right corner of the selected cell with the blue box, to copy the data down the column down as long as a neighboring cell has data. This saves a lot of time when you have a 7,000 row sheet you are working with.

5

Very similar to ceoliphant's answer but a little more straightforward, simply add one formula to C1: =ARRAYFORMULA(iferror(A:A*B:B*1.6))

5

You can write a custom Apps Script function with the Google Maps service, using a similar approach to this answer. To do this, open Tools...Script editor... and paste this function in: function getZIP(address) { var geo = Maps.newGeocoder().geocode(address); var resultComponents = geo.results.address_components; for (var i = 0; i < ...

5

You're nearly right. You can adapt your formula slightly like this: {=SUM(IF(A1:A5=B1:B5,1,0))} Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.

5

Simply click on the row's tag (the grey row headers on the left)

5

This formula implements the equation from Wikipedia: = -CEILING(-a_value - 0.5, 1) - 1 + ABS(SIGN(MOD(a_value, 2) - 0.5)) It works in Excel and Google Sheets. The spaces are optional.

5

4,8,2 Formulas for each number. =LEFT(A1, SEARCH(",",A1,1)-1) =MID(A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1)+1) - SEARCH(",",A1) - 1) =RIGHT(A1,LEN(A1) - SEARCH(",", A1, SEARCH(",", A1) + 1)) Source: https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/ Note: This will only work for three comma delimited numbers.

5

In Excel, one must put a formula in each cell that one is expecting a result. So, the short answer is no there is no function in Excel that would exactly match the use of Filter in Sheets. To do this I like to use INDEX with AGGREGATE: =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(\$D\$1:INDEX(D:D,MATCH("zzz",C:C)))/(\$D\$1:INDEX(D:D,MATCH("zzz",C:C))<>0),ROW(1:...

4

Here's an approach that works in Google spreadsheets: =COUNTIF(SPLIT(A1,","),"John")

4

Temporary solution Here's the temporary workaround that I came up with. Working formula Just use this formula: `=IF(LEN(A2)<3, CONCATENATE(REPT("0", 3-LEN(A2)), A2), A2)` Replace 3 with padding length and 0 with padding character. A2 is a padding source. Explanation Consider the following spreadsheet: ------------- | A | B | ------------- | 1 ...

4

On cell B3 put this: =\$B\$2*EXP(-A3/1000)+230 This formula uses a product of: The initial price \$B\$2: 2500 And the decaying factor: EXP(-A3/1000) plus a floor factor to avoid getting to zero (230). Now, drag this to copy to all the rows. The results are: Count Price 50 2500 100 2492 500 1746 1000 1150 2000 ...

4

Mathematics aside, you can use the following array formula : =MEDIAN(IF(COLUMN(A:Z)<=B2:B5,C2:C5)) CtrlShiftEnter Notes: IF(COLUMN(A:Z)<=B2:B5,C2:C5) generates a 2D array of 4 rows, 26 columns where each row repeats the cell in column C as many times as specified in the cell of column B; the remaining entries in the row are FALSE. The MEDIAN ...

4

First thing to figure out is if your dates are text or if they are numbers formatted by excel to display as dates. Its a very important distinction and you want the later as it allows you to use more of excels built in formulas. Some possible tests are =ISTEXT(A2) =ISNUMBER(A2) Change the cell formatting to general and see if the displayed contents change ...

4

You can use COUNTIFS: =COUNTIFS(\$A:\$A,E\$1,\$B:\$B,\$D2) The key here is the correct use of absolute / relative references.

3

This is rather simple using Funfun in Excel. Funfun allows you to use JavaScript directly in Excel so that you could define all sorts of functions by yourself. With the help of the Funfun Excel add-in, you could use the data in the spreadsheet and also output data into specific cells. Basically what you need to do is 1). Insert the Funfun add-in from ...

3

Another idea: Use the inbuilt filter functionality. Filter by the Tags column, then you can search for rows containing a given tag. A row with the tag list: comedy,horror,romance would show up when searching for any of those three tags.

3

In Excel you can use SUMPRODUCT function in a similar way to Andi's solution, but avoiding "array entry": =SUMPRODUCT(0+(A1:A5=B1:B5)) that will also count any blank rows (as does Andi's) so if you want to avoid that change to this version: =SUMPRODUCT((A1:A5<>"")*(A1:A5=B1:B5))

3

The algebraic formula for exponential decay is a•ekt where k is less than zero. In your example, a is 2526.78 (your price for 0 count), t will be column A. For k here is the formula =-LN(2526.78/300)/10000 I would place your high value in C2, your low value in D2 and the corresponding high count into E2, that way you only need to change then there to ...

3

The insert toggle is sneaky - it's hard to tell when it is on, and when your context is inside of the formula bar (ie you are editing a formula) it doesn't seem possible to turn it off - context has to be out of that formula bar. 1) Click into some other cell of the sheet 2) Press the Insert key. 3) Attempt to edit the formula again.

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