95
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 ...
33
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 ...
33
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 ...
21
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 ...
14
The key is to enter at least the first two values to get auto fill to work with a pattern
9
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
19.8k13 gold badges80 silver badges166 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, ","), ...
5
It is as simple as using the following sequence formula
=SEQUENCE(A1)
Or you even could have something like this:
=SEQUENCE(A2,1,A4,A6)
You could even use the SEQUENCE function for dates
=SEQUENCE(A2,1,A4,A6)
SEQUENCE
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.
4
Alternatives:
Use ArrayFormula / MMULT functions
Limitations: Some formulas should be "redesigned in order to be used with
ArrayFormula / MMULT or other similar built-in functions.
Related Q&A:
How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?
Make a custom function
Limitations:
Only works when ...
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
Slight improvement to formula in this other answer so in case that A1 is blank or if it has an empty string it will return zero.
=IF(LEN(A1)=0,0,COUNTA(SPLIT(A1," "))
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
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
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
...
Only top voted, non community-wiki answers of a minimum length are eligible
