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 -...
- 1,556
16
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 ...
- 366
11
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 ...
- 27.7k
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(...
- 23.5k
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 ...
- 181
7
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 ...
- 71
7
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.
...
- 413
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 ...
- 668
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 ...
- 23k
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 ...
- 26.3k
6
votes
Display real-time filtering data from another worksheet in Microsoft Excel
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 ...
- 2,973
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) + ...
- 28.1k
5
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
...
- 19.5k
5
votes
Accepted
how to pass a string value into COUNTIF?
The cell reference acts as a variable:
=COUNTIF(A1:A10,C11)
- 780
5
votes
Accepted
Include an "at" symbol ("@") in a text cell
Go to Tools -> AutoCorrect Options -> Options
Uncheck URL Recognition
Press OK.
- 19.7k
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))
- 21.7k
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:...
- 23.5k
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
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.
- 23.5k
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
- 161
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. ...
- 19.5k
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 ...
- 7,505
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,"&...
- 523
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))
- 23.5k
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 ...
- 3,306
4
votes
Accepted
How to get the type of a cell in Libreoffice Calc?
You should use the TYPE function which:
TYPE
Returns the type of value, where 1 = number, 2 = text, 4 = Boolean value, 8 = formula, 16 = error value, 64 = array.
If an error occurs, the function ...
- 19.7k
3
votes
Accepted
Is there a spreadsheet version of .rtf?
It depends on your requirements.
Comma separated values (.CSV) are just text files. Every spreadsheet program is able to open and import them.
Microsoft's .XLS format is rather universal. Most ...
- 73.8k
3
votes
Excel Conditional Formatting Escaping a Question Mark
Also you can use Specific Text > Contains > ~?
This tells excel to ignore symbol command and treats the cell as text.
- 31
3
votes
Does any spreadsheet allow you to create your own functions?
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,...
- 297
Only top scored, non community-wiki answers of a minimum length are eligible