All Questions
35
questions
0
votes
1
answer
42
views
Nested If statement. working in two sheets
its been a long time since I used Excell so my thinking may be complety wrong, but this wont work. Am I using the wrong Syntax for LibreOffice Calc
=IF(M3>$'Shipping Profile'.$A$3,then $'Shipping ...
- 4,058
1
vote
1
answer
1k
views
How do I apply the MATCH() function only to a filtered range? Or is there another way to do that?
I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:
A (with auto-filter against "2")
0
1
3
4
5
Now I want to find out whether one of the ...
- 2,423
1
vote
3
answers
139
views
Convert an Excel table from one structure to the other
Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...
- 8,931
-1
votes
1
answer
367
views
Find latest non-blank value no later than given date - and quickly
I'm really struggling to find a solution to this deceptively simple problem. I've even enlisted ChatGPT's help, but none of its solutions have worked. Maybe you can prove you're smarter than a machine ...
- 31
1
vote
1
answer
248
views
How to apply a calculation on a table column with a range of value per cell
I have a large table where in the first column for some row rather than just a single value there is a range e.g.
I want to apply a calculation on all the data elements, but since in the first column ...
- 26
0
votes
1
answer
1k
views
How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column
I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below:
CURRENT DATA
Product Type
Email
Product 1
...
- 8,931
7
votes
4
answers
30k
views
How to do a "great circle" calculation in MS Excel or LibreOffice?
I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called "...
3
votes
3
answers
3k
views
Using VLOOKUP for multiple lookup values, then summing the outputs
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of ...
- 1
0
votes
1
answer
468
views
LibreOffice Calc: Exclude rows from INDEX() reference
Working on Sheet2, I can use the following formula to look up the "Sheet2.B1"th match in Sheet1, column B corresponding to Sheet1, column C:
{=INDEX($Sheet1.$B$1:$B$9,SMALL(IF($Sheet1.$C$1:$...
- 19.1k
1
vote
1
answer
716
views
Neater way to ignore errors in LibreOffice Calc?
Let's consider any (potentially lengthy) function that can produce an error. For discussion's sake, I'll use =1/0. I can ignore the resulting error by using the following formula:
=IF(ISERROR(1/0),&...
- 21
0
votes
1
answer
53
views
Spreadsheet: How can I merge every nth line with a previous line?
I have a spreadsheet where each cell contains multiple key value pairs. Every value is on a new line. The content of every cell is the following: cell content I need to somehow merge the every second ...
- 19.5k
0
votes
1
answer
224
views
Conditional Format of cell based on the format of another cell
Referring to screen shot, I want to programmtically format a cell like so:
If cell B4 and cell F4 are both red text, then format cell J4 to be red text.
(and similarly for all other cells).
I see ...
- 314
0
votes
0
answers
30
views
Get addresses for all duplicates in a spreadsheet
I am trying to analyze output of performance benchmark. My spreadseet consists of multiple Linux top outputs spawned every 0,5 s for about a minute. I need to identify keywords that will help me ...
0
votes
1
answer
424
views
Lowercase only the leftmost character of a string in Excel
I have 2 columns containing strings. In the third column, I am trying to create a camelCase version of the strings, but I am stuck on how to accomplish the task. It has the following formula:
=...
- 1,402
1
vote
3
answers
425
views
How to create the equivalent of CHOOSE using a list in a cell reference
Selecting a specific value from a comma-delimited list can be performed with the CHOOSE function. For example:
=CHOOSE(A1,"A","B","C","D")
where A1 contains the index value, and "A","B","C","D" is ...
- 21.6k