Score of 31
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 -...
- reputation score 1833
Score of 15
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 ...
- reputation score 28194
Score of 12
How to compare and extract data from two CSV files?
tl;dr: grep -f users.csv inputs.csv | cut -d, -f 1,3
If Linux you can do as shown below. If Windows 11 and you have already installed Microsoft's Windows Subsystem for Linux (WSL), open a command ...
- reputation score 88707
Score of 8
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 ...
- reputation score 181
Score of 8
Accepted
How to sort a spreadsheet by "dan and kyu" rank?
This formula will convert a rank value from 30k,...,2k,1k,1d,2d,...,9d to a negative or positive number -30,...,-2,-1,0,1,...,8, respectively:
=LEFT(D4;LEN(D4)-1) * IF(RIGHT(D4,1)="k";-1;1) +...
- reputation score 251
Score of 7
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 ...
- reputation score 3081
Score of 6
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 ...
- reputation score 3485
Score of 6
Accepted
Include an "at" symbol ("@") in a text cell
Go to Tools -> AutoCorrect Options -> Options
Uncheck URL Recognition
Press OK.
- reputation score 21097
Score of 5
Accepted
how to pass a string value into COUNTIF?
The cell reference acts as a variable:
=COUNTIF(A1:A10,C11)
- reputation score 790
Score of 5
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
- reputation score 181
Score of 5
How do I stop Excel from changing cell range when moving data around my spreadsheet?
You can use INDIRECT(). Below I use SUM() but you can use COUNTIF(), etc. and it should work as well:
=SUM(INDIRECT("A1:A2"))
(Note in the formula, surround the range with quotes. It won't ...
- reputation score 3184
Score of 5
How to compare and extract data from two CSV files?
PowerShell solution:
$users = @{}
Get-Content subset.csv | % { $users[$_] = $true }
Get-ChildItem *.csv | % {
Import-Csv $_.Name -Header Email,Data1,Data2,Data3,Data4 |
? { $users....
- reputation score 17383
Score of 4
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 ...
- reputation score 460
Score of 4
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.
- reputation score 23948
Score of 4
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. ...
- reputation score 19704
Score of 4
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 ...
- reputation score 7625
Score of 4
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,"&...
- reputation score 543
Score of 4
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))
- reputation score 23948
Score of 4
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 ...
- reputation score 21097
Score of 4
Accepted
How to highlight all cells that contain formulas
This is not difficult to implement.
First, let's figure out how a cell with data differs from a cell with a formula. For your example, the distinguishing feature would be the = sign in the leftmost ...
- reputation score 1988
Score of 4
Accepted
How can I stop libre office sheets jumping when scrolling wide columns?
This is not currently possible.
A ticket already exists that has been open since 2011 (with no meaningful progress).
Sources: [1] [2] [3] [4]
- reputation score 1914
Score of 4
Accepted
How to perform an "on this date" check with a spreadsheet formula?
Your formula is alright, but it fails because your end date could be year+1 from the start date. You must also account for that scenario.
Update that to:
=OR(
AND(
DATE(YEAR(A1),MONTH(TODAY()),...
- reputation score 2082
Score of 3
Accepted
How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text
So after lots more research and some very helpful input from @ProfoundlyOblivious, I worked out the best possible method that seems to work. It's far from perfect, but it actually works from all data ...
- reputation score 363
Score of 3
keyboard equivalent of dragging the little black square to generate sequence
The keyboard equivalent to the requested function is placed under the Edit->Fill->Series command in the menu. In newer LibreOffice versions the location is Sheet->Fill Cells->Series....
Before ...
- reputation score 161
Score of 3
How do I turn my CSV formatting into a automatic "drag & drop" BAT file?
Ctrl+H
Find what: ^(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+\R\*FROM CLIP NAME:\h*(.+?)\h*\R.+
Replace with: $1,$2,$4,$3,$5,$6,$7,$8,"$9"
check Wrap around
check Regular ...
- reputation score 19684
Score of 3
Ascending order of Excel rows
When sorting text, Excel looks at character codes which can be obtained using the CODE function. CODE(".") returns 46 while CODE("0") is 48 so the text with "." in the 3rd position is seen as less ...
- reputation score 522
Score of 3
Simple pattern with Excel COUNTIF to match a substring?
Another option is to use SUMPRODUCT and FIND:
=SUMPRODUCT(--(ISNUMBER(FIND(C11,A1:A10))))
- reputation score 6059
Score of 3
Accepted
Devising Excel Spreadsheet Formulas
You can use either of the formulas,
=SUMIF(J:J,"1100",H:H)
or even this one
=SUMPRODUCT(($J$2:$J$17="1100")*($H$2:$H$17))
Adjust cell references in the formula as needed.
- reputation score 9427
Score of 3
Accepted
Blank function bar
Follow these steps:
Select the cell E4, or range of cells where you want to hide formula/formulas.
Right click and select Format cells option from the menu.
Hit Protection TAB.
Protection TAB has two ...
- reputation score 9427
Score of 3
Accepted
countif value equals value in another column
In B8 enter:
=SUMPRODUCT(--($A2:$A6=B2:B6))
and copy across.
The formula in B8 just compares column B to column A (row-by-row) and counts the matches.For more info see:
xlDynamic
EDIT#1:
The ...
- reputation score 19704
Only top scored, non community-wiki answers of a minimum length are eligible