74
From https://support.google.com/docs/answer/6325535?hl=en :
Open a spreadsheet in Google Sheets.
Paste the data you want to split into columns.
In the bottom right corner of your data, click the Paste icon.
Click Split text to columns. Your data will split into different columns.
To change the delimiter, in the separator box, click
PS: There is no option ...
30
Assuming you want:
Row | A | B | B's formula
--------------------------------
1 | 1 | 1 | =1 (or =A1)
2 | 2 | 3 | =1+2 (or =A2+B1)
3 | 3 | 6 | =1+2+3 (or =A3+B2)
etc...
Then you can use:=sum($A$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up ...
29
Yes:
=ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10))
The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect.
This spreadsheet that I made a while ago attempts to explain how MMULT can be ...
18
As it was already pointed out in other answers, you have to select at least two cells containing successive dates.
However, this didn't work for me at first because my Google Sheet had the wrong locale and the cells had the wrong formatting.
Set the locale for your Google Sheet:
File menu → Spreadsheet settings... → Set the correct locale
Configure the ...
13
The key is to enter at least the first two values to get auto fill to work with a pattern
10
In Google Spreadsheets, I would do it a bit different.
Formula
=COUNTA(SPLIT(A1, " "))
Explained
The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.
The Excel formula gives the same answer, but a bit more laborious:
=LEN(TRIM(A1))-LEN(...
answered Nov 15 '13 at 19:08
Jacob Jan Tuinstra
18.4k12 gold badges72 silver badges162 bronze badges
6
TL;DR
=AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))
Example
Explanation
First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be counted. Our example would give us 9
From this number we subtract 7 since you want the average of the last 7 values: =COUNT(A:A)-7. This will give us a 2.
Now ...
5
You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere,
= TRANSPOSE(A1:A3)
will fill column as below,
First member
Second member
Third member
To repeat the column before and after team members, you can try,
= TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), ...
4
Short answer
Use ARRAY_CONSTRAIN(array,row_limit,column_limit)
Explanation
ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.
To calculate the number of rows/columns ...
4
Formula
=ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0)))
Explanation
LARGE returns the nth greater element.
IFERROR if the first argument returns an error, returns the second argument.
{1,2,3} is an array of literals. See Using arrays in Google Sheets.
ArrayFormula makes that functions like LARGE and IFERROR return an array of values.
3
In array formulas instead of AND and OR functions use * and + respectively.
Example
AND: Instead of
=ArrayFormula(and(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4*B2:B4)
OR: Instead of
=ArrayFormula(or(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4+B2:B4)
3
Please try:
Find: (.+)
Replace with: $1-banner.jpg
Search: This sheet
Search using regular expressions
Replace all.
3
This formula in essence will do that.
Formula
=ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, ""))
Explained
The IF statement, in combination with the ARRAYFORMULA, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing.
Note
Now that you have the solution you want, you're not ...
3
It is possible to make a union in Google Spreadsheet very easily nowadays with this formula:
={'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A}
See more info in Google Docs Help: Using arrays in Google Sheets
3
You could split the cell's value into individual cells. Given that cell A1 contains the number 2113, put the following in cell B1:
=SPLIT(REGEXREPLACE("" & A1, "(\d)", "$1,"), ",")
This should result in cells B1:E1 containing each respective digit.
What the formula does is three things:
The expression "" & A1 converts the input number 2113 to a
...
3
My problem was that Google Sheets did not recognise what I was typing in as a date (24.05.2017) so when I changed it to 24/05/2017 it dragged consecutive dates immediately without having to highlight any other cells.
3
Formula
=ArrayFormula(HLOOKUP(A1:H1,Data!A:I,ROW(Data!A2:A),FALSE))
Explanation
Not all the Google Sheets formulas "iterate" when they are nested inside ArrayFormula by the other hand, FILTER is similar to ArrayFormula as it returns an array of values.
Instead use nest ROW inside HLOOKUP, as it's shown on the formula section of this answer.
3
You probably want to look at LARGE().
Returns the nth largest element from a data set, where n is user-defined.
The simplest, but by no means most elegant, solution I have is:
=if(iserror(large({data},1)),0,large({data},1))+
if(iserror(large({data},2)),0,large({data},2))+
if(iserror(large({data},3)),0,large({data},3))
(carriage returns added for ...
Only top voted, non community-wiki answers of a minimum length are eligible
