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 ...
1
vote
3
answers
138
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
...
- 270
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
votes
1
answer
365
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 ...
- 225
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
...
- 135
1
vote
1
answer
710
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
467
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:$...
- 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 ...
- 1
0
votes
1
answer
220
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
415
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:
=...
- 211
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 ...
- 31
-3
votes
2
answers
4k
views
Excel: how to count and group rows
I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert).
I need to count each name of Column A and count each name of Column B. And then I need ...
- 99
10
votes
2
answers
759
views
Convert the multi-step sum of booleans into a single formula
I have a few sensor readings and I'm checking to see if the values are in the acceptable range.
To do this I use an IF to check if the values are > or < the calculated (average) value. The ...
- 203