31
votes

### Formula to see if a series of numbers is increasing in value?

You can use array formulae to accomplish this:
Assuming your data is in A1:J1, then enter the following formula into a cell =AND(B1:J1>A1:I1) and press Ctrl+Shift+Enter to create an array formula -...

15
votes

### How to create a formula for every row in a column in Google SpreadSheet?

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 ...

10
votes

Accepted

### Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

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(...

10
votes

Accepted

### How to find cells that reference the current cell in LibreOffice Calc spreadsheet?

LO Calc has built-in functions for that equivalent to Excel's audit functions. In Calc, it's called Detective, and is available from the Tools tab of the menu. Tracing precedents and dependents also ...

8
votes

### Formula to see if a series of numbers is increasing in value?

Since you tagged google-spreadsheets, you can also use an ArrayFormula to simplify this in one formula:
=AND(ARRAYFORMULA(B1:J1>A1:I1))
You can adjust the J1 and I1 accordingly if the number of ...

7
votes

### Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

From menu: Format → AutoFormat Styles.. . You could add your own.
Another option is to use macros (as in ngulam's answer).
Color2Rows is an extension that adds a button to the tool bar, for ...

7
votes

Accepted

### Highlight the cell if greater than value in the next column

Here are the steps for Excel. Assuming the first value is in A1,
select A1 to B3
click Conditional Formatting > New Rule
click Use a formula to determine
enter this formula into the formula box and ...

7
votes

Accepted

### Excel is changing my time input (32:00:00 becomes 8:00:00)

Excel isn't changing the cell's value.
This is evidenced by the fact your sum operation is returning the correct result.
Instead, Excel is formatting the cell in 24-hour time notation. In this ...

6
votes

### Is it possible to copy & paste a pivot table in a spreadsheet?

Select the Pivot Table
On the Analyze Ribbon - Click on Select-> Entire Pivot Table
Right Click Copy in the pivot table area
Go to the location where you want to place the copy of the pivot table and ...

6
votes

### How to create a formula for every row in a column in Google SpreadSheet?

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))

6
votes

### How to create a formula for every row in a column in Google SpreadSheet?

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.
...

5
votes

### How to create a formula for every row in a column in Google SpreadSheet?

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 ...

5
votes

### Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

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) + ...

5
votes

Accepted

### how to pass a string value into COUNTIF?

The cell reference acts as a variable:
=COUNTIF(A1:A10,C11)

5
votes

Accepted

### Include an "at" symbol ("@") in a text cell

Go to Tools -> AutoCorrect Options -> Options
Uncheck URL Recognition
Press OK.

4
votes

### Format percentages in Microsoft Excel

Found this answer on a different site:
Right Click, Format Cells, Custom. Manually type in "0.0\%" without
the quotes.
If you want more decimal places to show, just add in zeroes. "0.000\%"
...

4
votes

Accepted

### How to combine multiple formulas in 1 cell

You can nest functions inside each other, I think this is the one you need:
=IF(AND(C18>0, C6>J11), J11, IF(AND(C17 <K5, C6>J10), J10, C6))

4
votes

Accepted

### Multiline cells are displayed as single line cells

Once you have data in cells, run this short macro:
Sub FixHardReturns()
Dim CH As String, r As Range, v As Variant
CH = Chr(10)
For Each r In ActiveSheet.UsedRange
v = r.Value
...

4
votes

Accepted

### SUM depending on column value

You can do this with SUMIF():
=SUMIF(C:C,"Niels",B:B)
You can also use the data in E directly in the formula so you do not need to retype the formula for each, in E2 put just Niels
=SUMIF(C:C,E2,B:...

4
votes

Accepted

### How to compare two Excel spreadsheets?

It’s convenient that your spreadsheet uses 50 columns,
because that means that columns #51, #52, …, are available.
Your problem is fairly easily solved with the use of a “helper column”,
which we can ...

4
votes

### How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text

Here are three ways that meet the requirements and a 4th way that's more situational. Yes, 2 of them you said not to work by OP. They work for me on Office 365 Pro Plus so they will work for many ...

4
votes

### Microsoft Excel: display realtime filtering data from another worksheet

Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in ...

4
votes

### Using VLOOKUP for multiple lookup values, then summing the outputs

Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.

4
votes

### Simple pattern with Excel COUNTIF to match a substring?

concat the regex string and the cell string within the formula as such:
=COUNTIF(A1:A10,"*"&C11&"*")
let me know if this helped

4
votes

Accepted

### How to combine two rows in a spreadsheet into a single row?

In A3 enter:
=IF(COLUMNS($A:A)<COUNTA(1:1)+1,INDEX(1:1,COLUMNS($A:A)),INDEX(2:2,COLUMNS($A:A)-COUNTA(1:1)))
and copy across.
This should work for different lengths of data in the first two rows. ...

4
votes

Accepted

### Excel: How to extract repeated values from randomly ordered list?

In T1 you can use the below formula, then drag down for the other names.
=SUMIFS(Q:Q,O:O,S1,P:P,"HEIGHT")/SUMIFS(Q:Q,O:O,S1,P:P,"WEIGHT")
Ahh this answer was to go with the ...

4
votes

Accepted

### CONCATENATE cells in a column IF condition in other colum is met

In Excel 2019/365 you can do:
=TEXTJOIN(,TRUE,IF(A1:A4="include",B1:B4,""))
In Google Sheets:
=ArrayFormula(TEXTJOIN("",true,IF(A1:A4="include",B1:B4,"&...

4
votes

Accepted

### How do I return a column value based on whether another column is within an array range - Libreoffice Calc

VLOOKUP will not work as the lookup column is right of the return column. Use INDEX/MATCH:
=INDEX(A!A:A,MATCH(A1,A!B:B))

4
votes

Accepted

### How to sum a column based on existance of value in another column

The Syntax for SUMIF, according to the relevant microsoft support article:
SUMIF(range, criteria, [sum_range])
For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in ...

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

#### Related Tags

spreadsheet × 690microsoft-excel × 475

worksheet-function × 213

google-spreadsheets × 97

microsoft-excel-2010 × 70

libreoffice-calc × 69

openoffice × 51

openoffice-calc × 42

libreoffice × 39

iwork-numbers × 25

microsoft-excel-2007 × 24

microsoft-excel-2013 × 24

microsoft-office × 22

csv × 20

microsoft-excel-2016 × 16

conditional-formatting × 15

database × 14

sorting × 14

vba × 12

macros × 12

google-docs × 12

pivot-table × 10

cells × 10

automation × 9

date × 9